Automatic countdown timer to adjust database record

Question

I have a small MVC website which is for a friends Hair Salon. On this page I have a div which is used to display a number which it takes from a database record. This number is the current number of people sitting in the queue waiting for a haircut.

What I have currently is the ability to logon to an "admin" page and update this number using a form, from say "2" to "5", then change "5" to "6" dependant on how many people are sitting in this queue.

This is a manual operation as it currently stands. Code is below:

=============================

Controller

[HttpPost]
        public ActionResult Update(Data data)
        {
            if (ModelState.IsValid)
            {
                data.ID = 1; //EF need to know which row to update in the database. 
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index", "Home");
            }

            return View(data);
        }

====================================

Model code

{
    public class Data
    {
        public int ID { get; set; }
        public string Queue_Number { get; set; }
    }

    public class DataDBContext : DbContext
    {
        public DbSet<Data>Queue { get; set; }
    }
}

What I would really like to happen is that once you have manually updated the Queue Number from the form on the "admin" page I'd like an automatic count down of 20 minutes (the rough time it takes for the haircut) and then have the Queue Number auto-adjust down by one till it gets to "0".

e.g. We have 5 people in the queue, 20 minutes later it is auto adjusted to 4 people and the web page will auto update / refresh, then 2 more people walk in so we manually adjust it to 6 people in the queue and the timer starts again, each 20 min passes the queue is adjusted by -1 till it gets down to "0". Once it gets to "0" it stays there until we manually add more people to the queue.

I'm afraid I have no idea how to even begin with such a request, or even if it is possible?

I'd be really thankful for any help from the experts here that might be able to "babystep" it for me. Any information I've not provided I'll endeavour to add - I realise I'm not the best at explaining myself :-(

Gigantopithecus

Answer 1

Have you considered Ajax? are you storing the last updated time on manually setting the flag? You can use Ajax request to simultaneously run using jquery Set interval. which will trigger the ajax request every 2 minutes. Find the last time it was updated, if that is passed 20 minutes then remove one from the database, your return would be the new number and jquery can update that number for you.

Quite a simple process actually but need more detail on the underlying data.

Here is how I can see it working from your question

In Controller

public ActionResult ajaxUpdate()
        {
            //open connection
            dbcontext db = new dbcontext();
            db.Connection.Open();

            // get the last updated record in the database.
            var entry = db.Entry.OrderByDecending(m=> m.LastUpdatedDate).FirstOrDefault();

            //clean up
            db.Connection.Close();
            db.Dispose();

            //return -1 as error
            if(entry == null){

                return Json(-1,JsonRequestBehavior.AllowGet);

            }

            // get current number of people in queue
            Int32 numberOfPeople = entry.QueueNumber;

            TimeSpan span = DateTime.Now.Subtract(entry.LastUpdatedDate);

            if(span.Minutes >= 20){

                // if 20 mins have passed assume a person has been completed since manual update
                numberOfPeople--;

            }

            //this returns a number, alternatively you can return a Partial
            return Json(numberOfPeople, JsonRequestBehavior.AllowGet);
        }

Jquery and Ajax

$(document).ready(function () {

    // run function every x minutes
    setInterval(function () {
        UpdateQueue();
    }, 100000);





});
    function UpdateQueue() {

    $.ajax({
        cache: true,
        type: 'POST',
        url: "/ControllerName/ajaxUpdate",
        async: false,
        dataType: "json",
        success: function (result) {
            // on success result will be the number returned

            // -1 is error
            if (result == -1) {
                return;

            }

            // check the -- didn't return a negative
            if (result < 0) {

                result = 0;

            }

            //find your element in the HTML to update
            $('#NumberElement').text().replaceWith(result);


        }

    });


}

You must ensure you include your jquery libraries before you include this code or you will have Jquery not defined.

Definitive

Thanks Craig, I haven't considered Ajax, mainly because I have only just started to use MVC, and have no coding background, so wouldn't really have a clue where to start. I appreciate the reply though. I haven't had any thoughts on how to approach this yet, part of the question was to get ideas on what would be a good idea on how to achieve what I want. I am currently only storing the Queue number in the database, nothing else. – Gigantopithecus

@Gigantopithecus Look to store the time and then use jquery and Ajax to update the number without any refreshing. There is a lot of documentation on Ajax and code samples. I will update the answer to get you going. – Definitive

Thank you Craig, I'll start researching Ajax as well. I appreciate your help, thanks again. – Gigantopithecus

Hi Craig, one little issue popping up, getting an error in the line of code: dbcontext db = new dbcontext(); - the "dbcontect" is underlined in read and the following error shows up "The type or namespace 'dbcontext' could not be found (are you missing a using directive or assembly reference?)" Do I need to add a "Using" at the top of the code? Sorry about this :-( – Gigantopithecus

Would anyone be able to advise on the error? I've tried adding bits / changing bits, but to no avail yet. Thank you. – Gigantopithecus

@Gigantopithecus Hi dbContext() is a Linq to SQL construct. Have you used Linq beofre? You need to add a dbml file to your project then use the server explorer to drag your tables into the designer. You need to reference Linq in the project then change dbContext to whatever you named your dbml, the ..."context" will handle your connections etc to the database. N.B if you make any changes to the structure of the database you will need to delete and re-drag the items on. Look into linq and you will find your answer – Definitive

Thanks Craig, no, I've not used Linq before. I'll have a look into it. Thank again for taking the time to help, I appreciate the efforts. – Gigantopithecus

@Gigantopithecus No problem, Linq is a really clean way to communicate with your data, there is plenty of documentation out there. – Definitive

Answer 2

I have made up for you server side solution with a little bit threading. Hope I am correct on critical sections locks.

It has an advantage that admin of your application does not have to hang on the page to get number of current customers downcounted (like he should with ajax requests).

How it works

On 'number of customers' update it is starting (if necessary) new counting-down thread, which waits (sleeps) for predefined interval and then decreases the number.

public class CustomerAdminService
{
    // time in milliseconds it will take to decrease number of waiting customers 
    const int sleepTime = 10000;
    // current number of customers (just for simplicity - you can have it in db or somewhere else)
    static int numberOfCustomers;

    static Thread updaterThread;

    // object lock
    static readonly object locker = new Object();

    public int GetNumberOfCustomers()
    {
        return numberOfCustomers;
    }

    public void UpdateCustomers(int value)
    {
        lock (locker)
        {
            if (updaterThread == null)
            {
                //start new downcounting thread
                updaterThread = new Thread(new ThreadStart(UpdateWorker));
                updaterThread.Start();
            }
            SetNumberOfWaitingCustomers(value);
        }
    }

    private void SetNumberOfWaitingCustomers(int value)
    {
        numberOfCustomers = value;
    }

    // downcounting thread method
    private void UpdateWorker()
    {      
        while (true)
        {
            // sleep for predefined time
            Thread.Sleep(sleepTime);
            lock (locker)
            {              
                var number = GetNumberOfCustomers();             
                if (number <= 1)
                {
                    // if number of currents customers is now zero - end the downcounting thread
                    SetNumberOfWaitingCustomers(0);
                    updaterThread = null;
                    return;
                }
                SetNumberOfWaitingCustomers(number - 1);
            }
        }
    }
}

Comment: You can consider using jQuery for some timer down-counting script. Showing something like: You can be served in 40 minutes ;-)

Offense

Answer 3

Yes Ajax is the key. It can be used by your website to communicate with your server unnoticeably.

Putupon

Answer 4

An alternative approach would be to not update the count in the database but simply use a query to determine the number of customers within a certain time period. You can do this by modifying the model so that instead of QueueNumber it uses an arrival time and changing the controller so that it inserts a new Data record.

{
    public class Data
    {
        public int ID { get; set; }
        public DateTime Arrival_Time { get; set; }
    }

    public class DataDBContext : DbContext
    {
        public DbSet<Data> Queue { get; set; }
    } 
}

This way, as others have suggested you can use AJAX to poll for the number of people in the queue with a controller action that might look something like this:

[HttpGet]
public ActionResult NumberOfPeopleInQueue()
{
    var result = db.NumberOfCustomersSince(DateTime.Now.AddMinutes(-20));
    return Json(result);
}

The nice thing about this approach is that should haircuts start to take longer (say 30 minutes) you can simply change the query and the application continues to work.

Desantis