37 – HOW TO KEEP THE PREVIOUS VERSION OF DATA OR THE MODEL WITHOUT USING TRIGGER (For Any Database Type)

HOW TO KEEP THE PREVIOUS VERSION OF DATA OR THE MODEL WITHOUT USING TRIGGER (For Any Database Type)

We have a look into this topic previously (Keeping the previous version of a Django model) but we used a trigger to implement the logic.

This was fine for only MySQL user only. If you are using PosgreSQL or another type of Database engine, you might have found that it wasn’t working.

In this video, I will show you how to archive this with any type of Database server, including SQLite3 database.

Django has inbuilt data manipulation functions that can be used to save data in the database. See examples in the codes below.

In this section of the tutorial, we are going to keep the previous version of our data and use it as our stock update history.

We can archive this by copying the content of the updated data into another table called StockHistory.

1. Create another table similar to the Stock table. We will use Django models to create it as follows:

class StockHistory(models.Model):
	category = models.ForeignKey(Category, on_delete=models.CASCADE, blank=True, null=True)
	item_name = models.CharField(max_length=50, blank=True, null=True)
	quantity = models.IntegerField(default='0', blank=True, null=True)
	receive_quantity = models.IntegerField(default='0', blank=True, null=True)
	receive_by = models.CharField(max_length=50, blank=True, null=True)
	issue_quantity = models.IntegerField(default='0', blank=True, null=True)
	issue_by = models.CharField(max_length=50, blank=True, null=True)
	issue_to = models.CharField(max_length=50, blank=True, null=True)
	phone_number = models.CharField(max_length=50, blank=True, null=True)
	created_by = models.CharField(max_length=50, blank=True, null=True)
	reorder_level = models.IntegerField(default='0', blank=True, null=True)
	last_updated = models.DateTimeField(auto_now_add=False, auto_now=False, null=True)
	timestamp = models.DateTimeField(auto_now_add=False, auto_now=False, null=True)

Note that we are setting all the fields to accept blank data blank=True, null=True and the date fields to auto_now_add=False, auto_now=False

This will give us the flexibility to pass data into it or leave it blank.

2. Do makemigration and migrate for the table called StockAudit to be created.
./manage.py makemigration
./manage.py migrate

3. Open MySQL workbench or any other database client, update the properties of the StockHistory table and remove all checkboxes on all fields.

4. Update the issue_item and receive_item view to include the functions that will copy the content of the new data into the history table. Then call the save function.

For Issue Items

issue_history = StockHistory(
	id = instance.id, 
	last_updated = instance.last_updated,
	category_id = instance.category_id,
	item_name = instance.item_name, 
	quantity = instance.quantity, 
	issue_to = instance.issue_to, 
	issue_by = instance.issue_by, 
	issue_quantity = instance.issue_quantity, 
	)
issue_history.save()

For Receive Items

receive_history = StockHistory(
	id = instance.id, 
	last_updated = instance.last_updated,
	category_id = instance.category_id,
	item_name = instance.item_name, 
	quantity = instance.quantity, 
	receive_quantity = instance.receive_quantity, 
	receive_by = instance.receive_by
	)
receive_history.save()

5: Make a view to list the content of the StockHistory table. Note that the queryset is defined to pull data from the StockHistory model and not Stock model. Don’t forget to import the StockHistory model.

@login_required
def list_history(request):
	header = 'LIST OF ITEMS'
	queryset = StockHistory.objects.all()
	context = {
		"header": header,
		"queryset": queryset,
	}
	return render(request, "list_history.html",context)

6. Create a template for history data and name it list_history.html

<div class="display_table">
        <table class='table'>
          <thead>
            <tr>
              <th>COUNT</th>
              <th>ID</th>
              <th>CATEGORY</th>
              <th>ITEM NAME</th>
              <th>QUANTITY IN STORE</th>
              <th>ISSUE QUANTITY</th>
              <th>RECEIVE QUANTITY</th>
              <th>LAST UPDATED</th>
            </tr>
          </thead>
        {% for instance in queryset %}
            <tr>

              <td>{{forloop.counter}}</td>
              <td>{{instance.id}}</td>
              <td>{{instance.category}}</td>
              <td><{{instance.item_name}}</td>
              <td>{{instance.quantity}}</td>
              <td>{{instance.issue_quantity}}</td>
              <td>{{instance.receive_quantity}}</td>
              <td>{{instance.last_updated}}</td>
            </tr>
        {% endfor %}
      </table>
    </div>

7: Make a URL for stockhistory list, and add the link in navbar.html
Add a URL in urls.py:

path('list_history/', views.list_history, name='list_history'),

8. Now add stock history link in navbar.html

<li class="nav-item">
   <a class="nav-link" href="/list_history">List History</a>
</li>