How to create master item form at the point of sale PHP. Finally, we have come to part 4, This article will discuss how to create a master item page where the system can add, edit and delete items from the database. Let’s start the following tutorial.
To start this section, you should read the 3 previous articles in order to understand it. In Part 1 I discuss how to create a login form. Then in part 2, I discuss how to create a dynamic menu on adminLTE. In part 3, I created a master user form to give users access to each menu. Here’s the previous part URL address
- Tutorial Build Point Of Sale With PHP, PDO, MySQL And Jquery Part 1.
- Dynamic Menu AdminLTE And Dashboard Page – Tutorial Build Point Of Sale With PHP, PDO, MySQL And Jquery Part 2.
- Create Form User – Tutorial Build Point Of Sale With PHP, PDO, MySQL And Jquery Part 3.
Let’s begin the tutorial.
Table of Contents
How To Create Master Item Page in Point Of Sale PHP
In this part 4 we will create four new files in the “sales” folder (pos/application/sales/): c_search_item.php, v_item.php, c_item.php and. j_item.js. Additionally, we will add a list of MYSQL queries to the master items (m_item table) in the pos.php file (pos/application/model/pos.php). Copy the following code in each PHP and javascript file.
c_search_item.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | <?php session_start(); require_ once ("../model/dbconn.php"); require_ once ("../model/pos.php"); function cmp($a, $b) { return strcmp($a["id_item"], $b["id_item"]); } $term = $_POST['term']; $pos = new pos(); $html =''; $data = $pos->autoCompleteItem($term); $hasil = array(); $i=1; foreach ($data[1] as $row) { $hasil[$i]['item_name'] = $row['item_name']; $hasil[$i]['id_item'] = $row['id_item']; $hasil[$i]['unit'] = $row['unit']; $hasil[$i]['price'] = $row['price']; $hasil[$i]['stock'] = $row['stock']; $i++; } usort($hasil, "cmp"); $no = 1; foreach($hasil as $key) { $html .= '<tr>'; $html .= '<td>'.$no.'</td>'; $html .= '<td>'.$key['id_item'].'</td>'; $html .= '<td>'.$key['item_name'].'</td>'; $html .= '<td style="text-align:right">'.number_format($key['price']).'</td>'; $html .= '<td style="text-align:right">'.$key['stock'].$key['unit'].'</td>'; $html .= '</tr>'; $no++; } $array = array(); $array['data'] = $html; echo json_encode($array); |
c_search_item.php used to show item list in the index page dashboard. After create this module, check your dashboard or index page and then try search the item in the text box. If successful the result like the following image:
v_item.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | <?php $titlepage="Master Item"; $idsmenu=1; include "../../library/config.php"; require_ once("../model/dbconn.php"); require_ once("../model/pos.php"); include "../layout/top-header.php"; include "../../library /check_login.php"; include "../../library /check_access.php"; include "../layout/header.php"; ?> <section class="content"> <div class="box box-success"> <div class="box-header with-border"> <h3 class="box-title">Master Item</h3> </div> <!--./ box header--> <div class="box-body"> <div class="row"> <div class="col-md-6"> <button type="submit" class="btn btn-primary " id="btnadd" name=""><i class="fa fa-plus"></i> Add Item</button> <br> </div> </div> <div class="box-body table-responsive no-padding" style="max-width:1124px;"> <table id="table_item" class="table table-bordered table-hover "> <thead> <tr class="tableheader"> <th style="width:40px">#</th> <th style="width:60px">Id</th> <th style="width:300px">Item</th> <th style="width:120px">Price</th> <th style="width:60px">Stok</th> <th style="width:250px">Note</th> <th></th> </tr> </thead> <tbody></tbody> </table> </div> </div> </div><!-- /.box --> </section><!-- /.content --> <div id="modalmasteritem" class="modal fade "> <div class="modal-dialog modal-md"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal">×</button> <h4 class="modal-title">Master Item Form</h4> </div> <!--modal header--> <div class="modal-body"> <div class="form-horizontal"> <div class="box-body"> <div class="form-group"> <label class="col-sm-1 control-label">Id</label> <div class="col-sm-11"><input type="text" class="form-control " id="txtiditem" name="txtiditem" value="*New" placeholder="" disabled=""><input type="hidden" id="inputcrud" name="inputcrud" class="" value="N"> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label">Item</label> <div class="col-sm-11"><input type="text" class="form-control " id="txtname" name="txtname" value="" placeholder="Please fill out item name"> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label">Stok</label> <div class="col-sm-11"><input type="text" class="form-control decimal" id="txtstock" name="" value="0" placeholder=""> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label">Unit</label> <div class="col-sm-11"><input type="text" class="form-control " id="txtunit" name="" value="" placeholder="Please fill out unit name"> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label">Price</label> <div class="col-sm-11"> <div class="input-group"> <span class="input-group-addon">Rp.</span> <input type="text" class="form-control money" id="txtprice" name="" value="" placeholder=""></div> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label">Note</label> <div class="col-sm-11"><textarea class="form-control " rows="3" id="txtnote" name="" placeholder="Note"></textarea> </div> </div> <div class="form-group"> <label class="col-sm-1 control-label"></label> <div class="col-sm-11"><button type="submit" title="Save Button" class="btn btn-primary " id="btnsaveitem" name=""><i class="fa fa-save"></i> Save</button> <span id="infoproses"></span> </div> </div> </div> </div> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> </div> <!--modal footer--> </div> <!--modal-content--> </div> <!--modal-dialog modal-lg--> </div> <?php include "../layout/footer.php"; //footer template ?> <?php include "../layout/bottom-footer.php"; //footer template ?> <script src="j_item.js"></script> </body> </html> |
v_item.php used to create the master item page.
c_item.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | <?php session_start(); require_ once ("../model/dbconn.php"); require_ once ("../model/pos.php"); if( isset( $_SERVER['HTTP_X_REQUESTED_WITH'] ) && ( $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' ) ) { $pos = new pos(); $method=$_POST['method']; if($method == 'get_detail_item') { $id_item=$_POST['id_item']; $pos = new pos(); $data = $pos->getItem($id_item); $array['data'] = $data[1]; $array['result'] = $data[0]; echo json_encode($array); } if($method == 'save_item') { $iditem = $_POST['id_item']; $nameitem = $_POST['item_name']; $unit= $_POST['unit']; $stock = $_POST['stock']; $price = $_POST['price']; $note = $_POST['note']; $crud=$_POST['crud']; $pos = new pos(); if($_POST['crud'] == 'N') { $array = $pos->saveItem($nameitem,$price,$unit,$stock,$note); if($array[0] == true) { $result['id_item'] = $array[2]; } } else { $array = $pos->updateItem($iditem,$nameitem,$price,$unit,$stock,$note); } $result['result'] = $array[0]; $result['error'] = $array[1]; $result['crud'] = $_POST['crud']; echo json_encode($result); } if($method == 'getdata'){ $pos = new pos(); $array = $pos->getListItem(); $data = $array[1]; $i=0; foreach ($data as $key) { $button = '<button type="submit" id_item="'.$key['id_item'].'" title="Tombol edit barang" class="btn btn-sm btn-primary btnedit " id="btnedit'.$key['id_item'].'" ><i class="fa fa-edit"></i></button> <button type="submit" id_item="'.$key['id_item'].'" title="Tombol hapus barang" class="btn btn-danger btn-sm btndelete " id="btndelete'.$key['id_item'].'" ><i class="fa fa-trash"></i></button>'; $data[$i]['price'] = number_format($data[$i]['price']); $data[$i]['DT_RowId']= $data[$i]['id_item']; $data[$i]['stock']= number_format($data[$i]['stock']); $data[$i]['button'] = $button; $i++; } $datax = array('data' => $data); echo json_encode($datax); } if($method == 'delete_item'){ $id_item=$_POST['id_item']; $pos = new pos(); $array = $pos->deleteItem($id_item); $data['result'] = $array[0]; $data['error'] = $array[1]; echo json_encode($data); } } else { exit('No direct access allowed.'); } |
c_item is a master item backend. All of PHP function to manipulate data in master item placed in this file.
j_item.js
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 | $(document).ready( function () { money(); decimal(); var value = { method : "getdata" }; $('#table_item').DataTable({ "paging": true, "lengthChange": false, "searching": true, "ordering": true, "info": false, "responsive": true, "autoWidth": false, "pageLength": 50, "dom": '<"top"f>rtip', "ajax": { "url": "c_item.php", "type": "POST", "data":value, }, "columns": [ { "data": "urutan" }, { "data": "id_item" }, { "data": "item_name" }, { "data": "price" }, { "data": "stock" }, { "data": "note" }, { "data": "button" }, ] }); $("#table_item_filter").addClass("pull-right"); }); $(document).on( "click","#btnadd", function() { $(".contentharga").remove(); $("#modalmasteritem").modal('show'); newitem(); }); function newitem() { $("#txtiditem").val("*New"); $("#txtname").val(""); $("#txtstock").val(0); $("#txtprice").val(0); $("#txtnote").val(""); $("#inputcrud").val("N"); $("#txtunit").change(); set_focus("#txtname"); } $(document).on( "click",".btnedit", function() { var id_item = $(this).attr("id_item"); var value = { id_item: id_item, method : "get_detail_item" }; $.ajax( { url : "c_item.php", type: "POST", data : value, success: function(data, textStatus, jqXHR) { var hasil = jQuery.parseJSON(data); data = hasil.data; $("#inputcrud").val("E"); $("#txtiditem").val(data.id_item); $("#txtname").val($.trim(data.item_name)); $("#txtunit").val($.trim(data.unit)); $("#txtstock").val(data.stock); $("#txtprice").val(addCommas(data.price)); $("#txtnote").val($.trim(data.note)); $("#modalmasteritem").modal('show'); set_focus("#txtname"); }, error: function(jqXHR, textStatus, errorThrown) { } }); }); $(document).on( "click","#btnsaveitem", function() { var id_item = $("#txtiditem").val(); var item_name = $("#txtname").val(); var unit = $("#txtunit").val(); var stock = cleanString($("#txtstock").val()); var price = cleanString($("#txtprice").val()); var note = $("#txtnote").val(); var crud=$("#inputcrud").val(); if(crud == 'E'){ if(id_item == '' || id_item== null ){ $.notify({ message: "Item Id invalid" },{ type: 'warning', delay: 8000, }); $("#txtiditem").focus(); return; } } if(item_name == '' || item_name== null ){ $.notify({ message: "Please fill out item name" },{ type: 'warning', delay: 8000, }); $("#txtname").focus(); return; } var value = { id_item: id_item, item_name: item_name, unit:unit, stock:stock, price:price, note:note, crud:crud, method : "save_item" }; $(this).prop('disabled', true); proccess_waiting("#infoproses"); $.ajax( { url : "c_item.php", type: "POST", data : value, success: function(data, textStatus, jqXHR) { $("#btnsaveitem").prop('disabled', false); $("#infoproses").html(""); var data = jQuery.parseJSON(data); if(data.ceksat == 0){ $.notify(data.error); }else{ if(data.crud == 'N'){ if(data.result == 1){ $.notify('Save item successfuly'); var table = $('#table_item').DataTable(); table.ajax.reload( null, false ); newitem(); }else{ $.notify({ message: "Error save item, error :"+data.error },{ type: 'danger', delay: 8000, }); set_focus("#txtiditem"); } }else if(data.crud == 'E'){ if(data.result == 1){ $.notify('Update item successfuly'); var table = $('#table_item').DataTable(); table.ajax.reload( null, false ); $("#modalmasteritem").modal("hide"); }else{ $.notify({ message: "Error update item, error :"+data.error },{ type: 'danger', delay: 8000, }); set_focus("#txtiditem"); } }else{ $.notify({ message: "Invalid request" },{ type: 'danger', delay: 8000, }); } } }, error: function(jqXHR, textStatus, errorThrown) { $("#btnsaveitem").prop('disabled', false); } }); }); $(document).on( "click",".btndelete", function() { var id_item = $(this).attr("id_item"); swal({ title: "Delete", text: "Delete master item with id : "+id_item+" ?", type: "warning", showCancelButton: true, confirmButtonColor: "#DD6B55", confirmButtonText: "Delete", closeOnConfirm: true }, function(){ var value = { id_item: id_item, method : "delete_item" }; $.ajax( { url : "c_item.php", type: "POST", data : value, success: function(data, textStatus, jqXHR) { var data = jQuery.parseJSON(data); if(data.result ==1){ $.notify('Delete item successfuly'); var table = $('#table_item').DataTable(); table.ajax.reload( null, false ); }else{ $.notify({ message: "Error delete item, error :"+data.error },{ type: 'eror', delay: 8000, }); } }, error: function(jqXHR, textStatus, errorThrown) { } }); }); }); |
Add the following code to pos.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 | /****************************************************************************** START TABEL m_item *******************************************************************************/ public function getListItem() { $db = $this->dblocal; try { $stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM m_item t, (SELECT @rownum := 0) r ORDER BY id_item ASC"); $stmt->execute(); $stat[0] = true; $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } public function getItem($id_item){ $db = $this->dblocal; try { $stmt = $db->prepare("select a.* from m_item a where a.id_item = :id "); $stmt->bindParam("id",$id_item); $stmt->execute(); $stat[0] = true; $stat[1] = $stmt->fetch(PDO::FETCH_ASSOC); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } public function updateItem($iditem,$item_name,$price,$unit,$stock,$note) { $db = $this->dblocal; try { $stmt = $db->prepare("UPDATE m_item SET item_name = UPPER(:item_name), unit= :unit, stock= :stock, price= :price, note= :note WHERE id_item= :iditem;"); $stmt->bindParam("iditem",$iditem); $stmt->bindParam("item_name",$item_name); $stmt->bindParam("price",$price); $stmt->bindParam("note",$note); $stmt->bindParam("unit",$unit); $stmt->bindParam("stock",$stock); $stmt->execute(); $stat[0] = true; $stat[1] = "Success Edit!"; return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } public function saveItem($item_name,$price,$unit,$stock,$note){ $db = $this->dblocal; try { $stmt = $db->prepare("call saveItem(:item_name,:unit,:stock,:price,:note)"); $stmt->bindParam("item_name",$item_name); $stmt->bindParam("price",$price); $stmt->bindParam("note",$note); $stmt->bindParam("unit",$unit); $stmt->bindParam("stock",$stock); $stmt->execute(); $stat[0] = true; $stat[1] = "Success save!"; $stat[2] = $stmt->fetchColumn(0); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } public function deleteItem($iditem) { $db = $this->dblocal; try { $stmt = $db->prepare("delete from m_item where id_item = :id"); $stmt->bindParam("id",$iditem); $stmt->execute(); $stat[0] = true; $stat[1] = "Success Delete!"; return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } public function autoCompleteItem($term) { $trm = "%".$term."%"; $db = $this->dblocal; try { $stmt = $db->prepare("SELECT a.* FROM m_item a WHERE item_name like :term or id_item like :term order by item_name asc"); $stmt->bindParam("term",$trm); $stmt->execute(); $stat[0] = true; $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } /******************************************************************************* END OF TABEL M_Item *******************************************************************************/ |
Don’t forget to add new table m_item with table structure like the following image:
Add new store procedures in the database. Execute the script below to create saveItem store procedures
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | DELIMITER $$ USE 'pos'$$ DROP PROCEDURE IF EXISTS 'saveItem'$$ CREATE PROCEDURE 'saveItem'(var_item_name VARCHAR(100),var_unit VARCHAR(4),var_stock INT,var_price INT,var_note TEXT) BEGIN DECLARE newnumber,newiditem VARCHAR(6); DECLARE countitem INT; SELECT COUNT(id_item) FROM m_item INTO countitem; IF(countitem = 0 ) THEN SET newnumber = '1'; ELSE SELECT TRIM(CAST((CAST(SUBSTR(id_item,3,6) AS UNSIGNED)+1) AS CHAR(4))) FROM m_item ORDER BY id_item DESC LIMIT 1 INTO newnumber; END IF; IF (LENGTH(newnumber) = 1) THEN SET newiditem=CONCAT('PB000',newnumber); ELSE IF (LENGTH(newnumber) = 2) THEN SET newiditem=CONCAT('PB00',newnumber); ELSE IF (LENGTH(newnumber) = 3) THEN SET newiditem=CONCAT('PB0',newnumber); ELSE SET newiditem=CONCAT('PB',newnumber); END IF ; END IF ; END IF ; INSERT INTO m_item(id_item,item_name,unit,stock,price,note) VALUES(newiditem,var_item_name,var_unit,var_stock,var_price,var_note); SELECT newiditem AS id_item; END$$ DELIMITER ; |
Until this part, We have created 5 tables and a MySQL store procedure.
Please test in your browser and make sure the system works as shown below:
Up to this point, the tutorial how to create master item page in point of sale php has been completed. The last step we will make the core of this application: making a sales system/simple point of sale in php and adminLTE. If there is any question please fill in the comment field at the end of the paragraph.
This complete point of sale project can be downloaded in part 5 of the article.
Leave a Reply