CODEWITHSUNDEEP
c#
Drake
Drake

Reputation: 13

increment logic

I have 2 columns in my database, intCaseID(int) and caseNo(nvarchar). I'm having a logic issue in which if a person submit a case i need to take care of the year and month.

So the case no will be (eg:20190301,Assuming today is 30 March 2019). But if the same person submit a case on a different date on 31 March 2019,it will be 20190302.02 denote case no. But if the date is 1 April 2019,the counter will reset to (20190401)

Below is how the logic should work

protected void Button1_Click(object sender, EventArgs e)
{
    string CaseNo="";

    //If today is 31 March 2019, filename = 20190301. If second person 
    //submit on same day, filename = 20190302
    //But if date is 1 April 2019, filename  = 20190401
}

Upvotes: 0

Views: 224

Answers (2)

Philani Mlawu
Philani Mlawu

Reputation: 11

One way to achieve your desired result would be to check the most recent case number in your database for that person, whenever a person is creating a new case.

Compare the month of the current date (which is when the new case number is being created) to the most recent case number. Should the two fall in the same month, then the new case number is just the old case number incremented by 1. Else the new case number has a new digit for the month value and the last value is set to 01.

Upvotes: 1

Luis Carlos Arellano Ochoa
Luis Carlos Arellano Ochoa

Reputation: 568

There are some parts that you will have to complete/fix since I don't know how you are connecting to your DB, but this will give you a general idea

protected void Button1_Click(object sender, EventArgs e)
{
    //If today is 31 March 2019, filename = 20190301. If second person 
    //submit on same day, filename = 20190302
    //But if date is 1 April 2019, filename  = 20190401

    DateTime processDate = DateTime.Now; // get current date
    string caseNo = "";
    string casePrefix = string.Format("{0}{1:D2}", processDate.Year, processDate.Month); // to help you search previous cases and create the new caseNo
    Int16 caseInMonthNumber = 0; // Int value of the case so you can increment
    string sqlResult = ""; // result from your query
    string sqlString = string.Format("SELECT caseNo from TABLE WHERE caseNo LIKE '{0}%' ORDER BY caseNo DESC LIMIT 1", casePrefix); // syntax might change depending on your DB


    // Function to call your database or whatever you are using to keep count of cases,  this will get you the highest value so the highest case... return the caeNo value
    // sqlResult = functionQueryToDB(sqlString); // or just send the prefix and create the 'sqlString' string in the function       

    //process the result from the database if there are no results, you don't need to do anything extra, start with caseInMonthNumber = 1,
    //but if you get a result, you need to read the last part of the string and increment by one... Depending on the logic for your program you might want to add
    // extra validations for the string
    if ( sqlResult != "" ) {
        if ( !Int16.TryParse(sqlResult.Substring(sqlResult.Length - 2) , out caseInMonthNumber) ) {
            //error handling, the last two digits are not a valid number
            return;
        }
    }

    caseNo = string.Format("{0}{1:D2}", casePrefix, ++caseInMonthNumber);

    // Do what you need with the new caseNo
}

Some things you need to consider:

  • What happens if you get more than 99 cases in a month
  • What happens if your app gets concurrent calls
  • What happens if you have users in different time zones, might want to use DateTime.UtcNow instead

Hope this helps.

Regards

EDIT: your function is void, I was returning and empty string in the error handling.

Upvotes: 1

Related Questions