Hey guys,
On the first Part, we created a Shopping Cart with the DataSore component and saw the manipulation of a Data Store containing Array Field using an alternative-temporary Data Store for the Array fields.
The Part-1 is here: Showcase: DataStore, Manipulating Array fields inside DataStore (Client_side)
On the second Part, we submitted our Order (Shopping Cart using the DataSore component with Array field) and Inserted the data in our database.
The Part-2 is here: Showcase: Part-2 / Database Insert, DataStore with Array fields - UPDATED
On this Part-2 we saw @Ben’s very important advice about sending sensitive data (product price) DIRECTLY from our Database and not from our DataStore because the inputs from the datastore are not to be trusted and can easily be changed by a skilled person on client-side.
Now in Part-3, we will pull the order’s data from our database and Update them.
As you can easily understand, this is not something that would happen in a public e-shop… This part is for Administration back-end use (logged-in moderators with granted permission can perform an Order Update) and as a matter of fact this whole Showcase was initially made for serving my needs in a project that no unauthorized user could have access.
In Part-3 there is a minor change in our DataStores schema because we cannot straight forward perform a Database Update (like the Database Insert in Part-2) in order to be able to track our existing Products and Variations (both primary and foreign keys).
At this point I would like to remind you that in our Order we can add many times the same Product (product_id) but with different Variations applied to it (or without Variation at all if it’s needed)
Here is our DataStores NEW SCHEMA:
In our “Order Update” procedure the changes that we are able to do are:
- Add new Products in our Order
- Add new Variations in our Products
- Delete Products
- Delete Variations in our Products
- Change Quantity and Price on any Product
We cannot:
- Change/Replace a Product with another (in this case we just delete the existing Product and add a new Product)
- Change/Replace a Product’s Variation with another (in this case we just delete the existing Variation and add a new Variation)
NOTE: the reason I didn’t get into these “we cannot” changes is not because it is impossible but because our code will be more complicated (for example, by changing a product will force our Variation List to be updated and all existing Variations for this product would be lost and…) and this is not what I want. So I decided to leave it simple and clear.
So, here we are in our “myorders” page and we see the basic Order List (filtered by loged-in user.identity for quick demostration, but we could easily filter it by any other user with a autocplete input or a userList and select from there):
Here we just click on an Order and an Order Preview section is appeared where we can just click the “Edit Order” button to open the Order in the “modal_Checkout”:
When Order is pulled form database we can see its details (Order_id, Order_Date) and the Products included in it:
Here we can:
- directly change the Quantity or Price,
- click to open the Product line in order to edit all of its features
- delete the Product Line
- Add a New Product Line
Let’s see the code behind these:
<p class="rounded bg-white fw-bold mb-0 pt-1 pb-1 ps-3 pe-2 lh-lg text-primary" dmx-text="srvc_OrderList.data.qr_userLoggedIn.usr_fname+' '+srvc_OrderList.data.qr_userLoggedIn.usr_lname+' ('+srvc_OrderList.data.qr_userLoggedIn.usr_username+')'">Client </p>
</div>
</div>
<div class="form-group mb-3 row">
<label for="ord_date" class="col-sm-2 col-form-label text-sm-end pe-0 fw-bold">Date :</label>
<div class="col-sm-10">
<input type="text" class="form-control text-primary bg-white fw-bold ps-3" id="ord_date" name="ord_date" placeholder="Date" is="dmx-date-picker" readonly="true" dmx-bind:customdates="srvc_ProdList.data.serverTime" customdates-start="srvc_ProdList.data.serverTime" customdates-end="srvc_ProdList.data.serverTime" dmx-bind:value="srvc_OrderDetails.data.qr_OrderDetails.ord_date">
</div>
</div>
</div>
</div>
<div class="row pt-2 px-1 small g-0 mt-sm-3" style="/* min-width: 760px */ /* overflow-x: auto */ white-space: nowrap;">
<h5 class="ps-2 fw-bold">Your Products:</h5>
<section class="row g-0 align-items-center py- border-bottom border-secondary bg-secondary bg-opacity-10 fw-bold">
<div class="col-auto align-self-center">
<button id="btn_UpsertProductLine1" class="btn btn-sm disabled border-0 btn-outline-secondary">
<i class="fas fa-pen-nib"></i>
</button>
</div>
<div class="pe-1 small text-center col-3 align-self-center">
<p class="mb-0 ms-1">Product</p>
</div>
<div class="pe-1 small text-center col-sm-1 col-2 align-self-center">
<p class="mb-0 text-center">Quantity</p>
</div>
<div class="pe-1 small text-center col-2 col-lg-1 align-self-center">
<p class="mb-0">Price</p>
</div>
<div class="pe-1 small text-center col-2 col-lg-1 align-self-center">
<p class="mb-0">Amount</p>
</div>
<div class="d-none d-sm-block col">
<div class="row g-0">
<p class="small text-center lh-sm mb-0 border-bottom border-secondary">Variations</p>
</div>
<div class="row g-0 align-items-center">
<div class="col-12 px-1">
<div class="row g-0">
<div class="col-5 ps-1 pe-0 small text-center bg-primary text-white">
<p class="mb-0 small lh-sm">Variation</p>
</div>
<div class="col-4 ps-1 pe-0 text-center small text-white bg-dark">
<p class="mb-0 small lh-sm">Value</p>
</div>
<div class="col-3 ps-1 pe-0 text-center small text-white bg-danger">
<p class="mb-0 small lh-sm">Price</p>
</div>
</div>
</div>
</div>
</div>
<div class="col-auto">
<button id="btn_DeleteProductLine1" class="btn btn-sm disabled border-0 btn-outline-secondary">
<i class="far fa-trash-alt"></i>
</button>
</div>
</section>
<div id="formRepeatProducts" is="dmx-repeat" dmx-bind:repeat="yourCart.data">
<div class="row g-0 align-items-center py-1 border-bottom border-dark fw-normal">
<dmx-value id="prod_index" dmx-bind:value="$index"></dmx-value>
<dmx-value id="prod_ID" dmx-bind:value="$id"></dmx-value>
<div class="col-auto">
<button id="btn_UpsertProductLine" class="btn btn-sm btn-primary shadow" dmx-on:click="run({meta:{$param:[{type:'number',name:'prod_ID'},{type:'number',name:'prod_index'}]},exec:{steps:{condition:{if:`prod_ID.value`,then:{steps:[{run:{action:`prodVars.clear()`,outputType:'text'}},{run:{action:`modal_UpsertProdLine.prd_indx.setValue(prod_index.value)`,outputType:'text'}},{run:{action:`modal_UpsertProdLine.prdid.setValue(prod_ID.value)`,outputType:'text'}},{run:{action:`data_detailProductLine.select(prd_id)`,outputType:'text'}},{run:{action:`srvc_VariationList.load({catid: data_detailProductLine.data.prd_cat})`,outputType:'text'}},{assign:{value:`$index`,name:'prodIndex',outputType:'text'}},{condition:{if:`yourCart.data[prodIndex.toNumber()].prod_vars.count()>0`,then:{steps:{repeat:{repeat:`prod_vars`,outputFields:['ord_prd_var_id','var_id','var_name','var_val_id','var_val_name','var_val_prc'],exec:{steps:{run:{action:`prodVars.insert({ord_prd_var_id: prod_vars[$index].ord_prd_var_id, var_id: prod_vars[$index].var_id, var_name: prod_vars[$index].var_name, var_val_id: prod_vars[$index].var_val_id, var_val_name: prod_vars[$index].var_val_name, var_val_prc: prod_vars[$index].var_val_prc})`,outputType:'text'}}},name:'repeat',output:true,outputType:'array'}}},outputType:'boolean'}},{run:{action:`modal_Checkout.toggle();modal_UpsertProdLine.show()`,outputType:'text'}}]},outputType:'boolean'}}}})" data-bs-target="#modal_UpsertProdLine">
<i class="fas fa-pen-nib"></i>
</button>
</div>
<div class="pe-1 align-self-center col-3">
<input id="ord_prd_id" dmx-bind:name="'formRepeatProducts['+$index+'][ord_prd_id]'" class="form-control bg-white" dmx-bind:value="yourCart.data[$index].ord_prd_id" readonly="true" type="hidden">
<input id="ord_product_id" dmx-bind:name="'formRepeatProducts['+$index+'][ord_product_id]'" class="form-control bg-white" readonly="true" dmx-bind:value="prd_id" type="hidden">
<legend dmx-text="prd_name" class="col-form-label-sm mb-0 ps-1 ms-1 border rounded">Enter your content here</legend>
</div>
<div class="pe-1 col-sm-1 col-2 text-center">
<input id="ord_prd_quantity" dmx-bind:name="'formRepeatProducts['+$index+'][ord_prd_quantity]'" type="number" class="form-control bg-white border form-control-sm text-center fw-bold" dmx-bind:value="prd_qnt.toNumber().toFixed(0)">
</div>
<div class="pe-1 col-2 col-lg-1 position-relative">
<input id="ord_prd_price" dmx-bind:name="'formRepeatProducts['+$index+'][ord_prd_price]'" type="number" class="form-control bg-white border form-control-sm text-end pe-3 ps-0" dmx-bind:value="prd_prc" dmx-on:updated.debounce:500="ord_prd_price.setValue(value.toNumber().toFixed(2))" value="0,00"><i class="fas fa-euro-sign position-absolute bg-secondary bg-opacity-10 text-primary translate-middle end-0 top-50 py-2 px-1 rounded" style="margin-right: -5px;"></i>
</div>
<div class="pe-3 text-end col-2 col-lg-1 border rounded position-relative">
<legend dmx-text="(ord_prd_quantity.value * ord_prd_price.value).toNumber().toFixed(2)" class="col-form-label-sm mb-0 w-100 ">0,00</legend>
<i class="fas fa-euro-sign position-absolute bg-secondary bg-opacity-10 text-primary translate-middle end-0 top-50 py-2 px-1 rounded" style="margin-right: -8px;"></i>
</div>
<div class="px-1 mx-0 d-none d-sm-block col align-self-stretch bg-light">
<div class="row g-0 align-items-center">
<div id="formRepeatVariations" is="dmx-repeat" dmx-bind:repeat="prod_vars" class="col-12">
<dmx-value id="variationIndx" dmx-bind:value="$index"></dmx-value>
<div class="row rounded rounded-1 bg-light border-info g-0 mt-0 mb-1 p-0 row-cols-3 border">
<div class="col-5 ps-1 pe-0 align-self-center fw-normal small">
<input id="ord_prd_var_id" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_id]'" class="form-control bg-transparent form-control-sm text-center" readonly="true" dmx-bind:value="yourCart.data[prod_index.value].prod_vars[$index].ord_prd_var_id" type="hidden">
<input id="ord_prd_var_Var_id" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Var_id]'" type="hidden" class="form-control bg-transparent form-control-sm text-center" readonly="true" dmx-bind:value="var_id">
<p dmx-text="var_name" class="mb-0 text-primary w-100 text-center lh-1" dmx-on:click="notifies1.danger('product='+prod_index.value+' ,Var='+variationIndx.value)">Var</p>
</div>
<div class="col-4 ps-1 pe-0 align-self-center fw-normal small">
<input id="ord_prd_var_Val_id" class="form-control bg-transparent form-control-sm text-center" readonly="true" type="hidden" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Val_id]'" dmx-bind:value="var_val_id">
<p dmx-text="var_val_name" class="mb-0 text-dark w-100 text-center lh-1">Val</p>
</div>
<div class="col-3 px-1 py-0 small">
<input id="ord_prd_var_Val_exprice" class="form-control lh-base bg-transparent border-0 py-0 px-0 text-danger text-end" readonly="true" type="number" dmx-bind:name="'formRepeatProducts['+prod_index.value+'][formRepeatVariations]['+$index+'][ord_prd_var_Val_exprice]'" dmx-bind:value="var_val_prc.toNumber().toFixed(2)" style="font-size: 1em;">
</div>
</div>
</div>
</div>
</div>
<div class="col-auto">
<button id="btn_DeleteProductLine" class="btn btn-danger btn-sm shadow" dmx-on:click="run({meta:{$param:[{type:'number',name:'prod_ID'}]},exec:{steps:{'bootbox.confirm':{message:'Delete Product... Are you sure?',title:'DELETE PRODUCT Confirmation...',buttons:{confirm:{label:'Yes, DELETE',className:'btn-danger'},cancel:{label:'CANCEL',className:'btn-secondary'}},swapButtonOrder:true,then:{steps:{run:{action:`yourCart.delete({$id: prod_ID.value})`,name:'del_ProdLine',outputType:'text'}}},name:'confirmDeleteProdLine'}}}})">
<i class="far fa-trash-alt"></i>
</button>
</div>
</div>
</div>
<div class="col-12 mt-2 mb-3 ps-2 pe-2">
<button id="btn_AddNewProductLine" class="btn btn-sm btn-warning border border-dark shadow rounded-pill fw-bold" dmx-on:click="modal_UpsertProdLine.show();modal_Checkout.toggle()">Add Product</button>
</div>
</div>
And the CANCEL and UPDATE ORDER code:
<!-- And the "CANCEL" and "UPDATE ORDER" buttons inside the Modal_Footer -->
<div class="modal-footer px-0 py-2 bg-secondary bg-opacity-25">
<div class="row justify-content-between w-100 my-0" style="z-index: 1;">
<div class="col-auto">
<button type="button" class="btn btn-secondary shadow my-0 w-auto" data-bs-dismiss="modal" dmx-on:click="frm_Order.reset(true);yourCart.clear();prodVars.clear()">CANCEL</button>
</div>
<div class="col-auto">
<button type="button" class="btn shadow my-0 btn-danger" dmx-on:click="frm_Order.submit()">UPDATE ORDER</button>
</div>
</div>
</div>
As we can see, no serious changes were made since our previous Part-2 demo in this form.
The minor change we made is to include in every product line (“formRepeatProducts” dmx-repeat) and each product’s Variation (“formRepeatVariations” dmx-repeat) hidden inputs for holding their primary keys (“ord_prd_id” for “order_products” subtable and “ord_prd_var_id” for “order_prod_vars” subsubtable)
All the job is being handled inside the serveraction “order_update”:
You can check the screenshot above but I just want to highlight the main steps:
1) Pull DB products for this order (qr_GetDBproducts)
2) Insert a repeat (“record_repeat”) based on qr_GetDBproducts and check if every DB Product’s (primary field) exists in our Form.
If not, we delete it from the database
3) So, now that all the unwanted products have been eliminated from our Database we insert a new repeat based on our $_POST.formRepeatProducts and output all of its fiels.
4) Inside this repeat we check if the current Form Product exists in Database and:
-
a) if exists, we perform a database Update (No foreign key field is assigned…):
-
b) if not exists, we perform a database Insert foreign key field is assigned in order to be related with our parent table:
5) Continueing inside the repeat, we pull our current Product Variations (gr_GetDBprodVariations):
6) Insert a repeat based on gr_GetDBprodVariations and check if every DB Product Variation (primary field) exists in our Form.
If not, we delete it from the database
7) Insert another repeat based on formRepeatVariations group of the main “record_repeat” repeat and loop through the Form’s current Product Variations.
Here we are going to check if the current Variation is new (database Insert) or exists in database (database Update).
IMPORTANT STEP:
If a new product is inserted and immediately add variations to it WE HOLD THE database insert.identity of the new product.
So, the value for the foreign key order_product_id will be the newly inserted id of the product and NOT the form’s ord_prd_var_id value (current Variation) that we pulled from inside the repeat
8) We check if the current Variation exists in DB and:
-
a) if exists, we perform a database Update (No foreign key field is assigned…):
-
b) if not exists, we perform a database Insert foreign key fields is assigned in order to be related with our parent table :
In this case the “order_product_id” (foreign key) is uqual to the Product’s database_insert.identity of the step-4b.
And we are done with the Database Update and obviously this is the end of this showcase…
AS ALWAYS… If anybody has a suggestion, recommendation, correction or comment PLEASE drop it here!
Thanks for reading.