• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

mvc: help sending value from view to controller/repo

I have an mvc app that I need help returning a claim number based on the year of a date field.  Technically I think this example might be considered model view view model.  Which im new to.  

The rendered page from the view looks like this.

the view
When the user clicks on the spy glass it should take the accident date and pass it to the controller and do a max of the claim number + 1 based on the year.  So if accident date is 12/21/2013 it should do a max(claimnumber) where year = 2013.  Then add 1 to that value to get the max claim number for that particular year.

The html for the claim number in the view is like so:
 <td class="editor-field">
                     @Html.TextBoxFor(model => model.ClaimNumber, new { size = 5 })
                      <input type="image" src="/RTIContent/search.jpg"  />  
 </td>

Open in new window

which creates this.
claimnumber
When the user clicks on the spyglass it invokes the create method in the controller.
public ActionResult Create(DriverVehicleAccidentViewModel model, string next)
        {
            using (_accidentRepository)
			 var accident = new RTI.Accidents.Data.Accident();
			   _accidentRepository.AddAccident(accident); 

Open in new window


The accident date section in the view looks like this.
accident info where date is
As you can see there is an accident date field in there created from the model.

The date field in the view is a part of something called accidentinformationeditor as you can see in the view html below.
 <td>@Html.EditorFor(model => model.Information, "AccidentInformationEditor", Model.Information)</td>

Open in new window


The accident repository contains a method called GetNextClaimNumber().
and returns it to the claimnumber control in the view.  The problem is it does not take into account the year of the accidentdate field.  So I need to figure out how to pass it
Year(AccidentDate).  Im not sure how.

   public void AddAccident(RTI.Accidents.Data.Accident entity)
        {
            entity.ClaimNumber = GetNextClaimNumber();
            CheckForNulls(entity);
            _objectSet.AddObject(entity);
        }


public int GetNextClaimNumber() 
        {
            var lastAccident = _context.Accidents.OrderByDescending(c => c.ClaimNumber).Take(1).ToList();
            var lastClaimNumber = lastAccident.Count > 0 ? lastAccident[0].ClaimNumber : 0;

            if (lastClaimNumber == 0 || (lastClaimNumber.ToString().Substring(0, 2) != DateTime.Now.Year.ToString().Substring(2, 2)))
            {
                return Convert.ToInt32(string.Format("{0}{1}", DateTime.Now.Year.ToString().Substring(2, 2), "0001"));
            }
            else
            {
                return ++lastClaimNumber;
            }
        }

Open in new window


If more code or model info is needed I can provide that so I can help you help me figure this out.

Thanks.
0
Steve Hougom
Asked:
Steve Hougom
  • 23
  • 17
1 Solution
 
Ioannis ParaskevopoulosCommented:
Hi,

Why don't you have your function accept date as a param:

Public int GetNextClaimNumber(int year)
ber() 
        {
            var lastAccident = _context.Accidents.Where(x=>x.AccidentDate.Year==year).OrderByDescending(c => c.ClaimNumber).Take(1).ToList();
            var lastClaimNumber = lastAccident.Count > 0 ? lastAccident[0].ClaimNumber : 0;

            if (lastClaimNumber == 0 || (lastClaimNumber.ToString().Substring(0, 2) != DateTime.Now.Year.ToString().Substring(2, 2)))
            {
                return Convert.ToInt32(string.Format("{0}{1}", DateTime.Now.Year.ToString().Substring(2, 2), "0001"));
            }
            else
            {
                return ++lastClaimNumber;
            }
        }

Open in new window


And call this as:

entity.ClaimNumber = GetNextClaimNumber(entity.AccidentDate);

Open in new window


Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Almost got it.  One problem.  Its still basing next claim number off 2014 vs the year I passed in.  See image below of the debug window.  You can see that the return value for claim number contains the 140005(14 is the year) when it should contain 12xxx.  12 obviously being the Year.  

debug window
But as far as passing in the value my gosh it was way easier than I thought because of entity framework.  

Thanks
0
 
Steve HougomDeveloper IIAuthor Commented:
Great help.  Turned out to be super easy.  Expert answered the original question.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Ioannis ParaskevopoulosCommented:
Hi,

Good to know i could help.

I would like to add a comment on how i would do it.

Since the next claim number is part and depends on the actual entity, i would make it either a read only property ( only a getter) or a method of the model. For instance:

public class MyEntity
{
public DateTime AccidentDate {get;set;}
.
.
.
public int NextClaimNumber
{
    get
    {
        return // your code
    }
}

}

This way whenever you access the property NextClaimNumber you will get the correct answer. Until the entity is saved you will always get the correct answer.

Another way( i prefer this one) would be a method:

public class MyEntity
{
    private int _NextClaimNumber {get;set;}
    public  int NextClaimNumber
    {
        get{return _NextClaimNumber;}
    }
    public DateTime AccidentDate {get;set;}
    public void GetNextClaimNumber()
    {
        // your code to get the max+1
        // when it comes to access the accident date just use this.AccidentDate.Year
        // so there is no need for an argument...
        _NextClaimNumber = // the number you got from your db
    }
}

I think the last option is more object oriented.

Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Yeah because whats happening now is the original developer is saving the the accident repository everytime and its creating unnecessary rows in the accident table.

                        _accidentRepository.SaveChanges();

Open in new window


If you look at the last few rows in the table its saving them even if I dont want them.  Im just clicking on the spyglass and when I do that its saving the row to the accident table even if I dont want the entire record.  Also check the claimnumber vs the accident date.  14xxx for 2012 accident date.  

accident table
In the model/vew the accident date and claim number are declared as below.

accident date declaration in model
claim number declaration
0
 
Ioannis ParaskevopoulosCommented:
I cannot comment on the SaveChanges as i do not see a full part of code where this is called, so if you still need help plz provide a more detailed piece of code.

But let's see why you have trouble with the correct numbering.

Take a look at this piece of code:


 
if (lastClaimNumber == 0 || (lastClaimNumber.ToString().Substring(0, 2) != DateTime.Now.Year.ToString().Substring(2, 2)))

Open in new window


Here you check if lastClaimNumber equals to 0 or its first two characters are different to the current years two last digits.

Lets take a record of 2012. The last record of it will be something like 1212154 (as seen on your example)
In this examply the two first characters of the last claim number (12) are different than the last two digits of the current year(14). So the if above returns true, so it gets in the first case, which initializes the Last Number as this year's two last digits (14) concatanated with "0001". This happened the first time the check was made for 2012.

Now, when you check again, the last claim number for 2012 is 140001. If you check the first digits, with the current year, these are equal, so the if statement is false, so it returns this number increased by 1, etc etc.

I think you should change != in the if statement with an ==.

Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Well in a nutshell this is what im trying to do.  Just having trouble with the coding of it.  Especially the linq parts.

var newclaimnumber

if Year(accidentdate) = 2012
   newclaimnumber= highest number 12xxx claimnumber add 1 to it

if Year(accidentdate) = 2013
   newclaimnumber = highest number 13xxx claimnumber add 1 to it.

if Year(accidentdate) = 2014
    newclaimnumber = highest number 14xxx claimnumber add 1 to it.

return newclaimnumber
0
 
Steve HougomDeveloper IIAuthor Commented:
And you can see that some of these test records already have messed up claimnumbers so the existing logic doesnt work.  e.g enter an accident date for 2012.  Well the highest one in the db right now has 140009 on it so the logic wont work.

data
0
 
Ioannis ParaskevopoulosCommented:
I think you are close:

public int GetNextClaimNumber() 
        {
            //get the accident's year
            int year = this.AccidentDate.Year;
            //get the last accident number (you do not need the whole row, as you actually do not do anything with it
            var lastClaimNumber = _context.Accidents.Where(x=>SqlFunctions.DatePart("year",x.AccidentDate) == year).OrderByDescending(c => c.ClaimNumber).FirstOrDefault().ClaimNumber;

            //the above will either return the last number for the year in question (First) or null (OrDefault), so there is only point to check if it is null, so it gets initiallized. This practically means that it is the very first record of a single year.
            if (lastClaimNumber == null)
            {
                //I believe the way i present it here is simpler. ToString can format DateTime, and if we request "yy" then we get the two digit year representation (14 for 2014), and Concat can save us some characters of code..., though i guess the changes i did here could add only in readability than anything else.
                return Convert.ToInt32(string.Concat(DateTime.Now.ToString("yy"), "0001"));
            }
            else
            {
                return ++lastClaimNumber;
            }
        }

Open in new window


I am getting the implementation where GetNextClaimNumber is a method of your model. Of course, you may pass the year as a param and make it just a function.

In case you are not aware of it, SqlFunctions is accessible via System.Data.Entity.SqlServer namespace.

Let me know how it goes ;)

Giannis
0
 
Ioannis ParaskevopoulosCommented:
In order to fix the messed up records i would suggest running an update query. The following may help:

;With FirstCTE AS
(
SELECT claimnumber,
             ROWNUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident]
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum),4) AS NewClaimNumber
FROM   FirstCTE
)
UPDATE [Accident].[tbAccident]
SET        claimnumber = NewClaimNumber
FROM    [Accident].[tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber

Open in new window


Before running the above, i would suggest you would check (with a select instead of thee update), that you get the desired results. And of course ALWAYS BACKUP.

If you have other tables with records depending on these claimnumbers that are about to change, the solution could be trickier.

I hope i helped,
Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
I had to change it a bit to get it to complile.  The parts I wasnt sure about were below.
int year = this.AccidentDate.Year; //couldnt get this to compile.  Wasnt sure if it was the passed in year from the UI or if its supposed to come from the accident repository.

Open in new window

Also I had to code the SQLFunction.DatePart like this.  Notice the System.Data.Objects.SqlClient.
System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", x.AccidentDateTime) == year)

Open in new window

Here is how it turned out in my debug run.  Notice the claimnumber still is 14xxxx.
debug
0
 
Ioannis ParaskevopoulosCommented:
Did you run the Update script?
0
 
Steve HougomDeveloper IIAuthor Commented:
I could not because of the following error.

'ROWNUMBER' is not a recognized built-in function name.

I think i have Sql Server Mgmt Studio for 2012 but it could be 2008.  So im not sure if your functions would work.  

It is staging data im working on so I dont mind fixing some of these.  But Im not sure how that would go in production.  I do realize that all things being equal if the existing claimnumbers match up with the accidentdate year then the logic would work.  However the system has to be so a user can backdate these or enter them way in the future such as for 2015 even.  So I guess I havent wrapped my head around how the update query would turn out.
0
 
Steve HougomDeveloper IIAuthor Commented:
Looks like I have 2008.

ssms
0
 
Steve HougomDeveloper IIAuthor Commented:
Im trying to do a linq statement like this to say take the top 1 record where the accidentdate
Year and the passed in Year are equal AND the ClaimNumber in db substring (0,2) are equal.

            var lastAccident = _context.Accidents.Where(x => x.AccidentDateTime.Year == accidentDateYear && x.ClaimNumber.ToString().Substring(0,2)=="12").OrderByDescending(c => c.ClaimNumber).Take(1).ToList();

Open in new window


Just a sample of what im trying above.  Not sure in linq if that is doable.  Maybe you know a way I could code that.  I just hard coded the "12" for understanding temporarily.
0
 
Ioannis ParaskevopoulosCommented:
I am really sorry... It id ROW_NUMBER. Forgot the underscore.

I suggest running this query first. Then you should be good to go
0
 
Steve HougomDeveloper IIAuthor Commented:
Just curious.  Is the numbering below correct?  I see 14's with the same row number set as the 12's.

result of first cte
Oh wait.  I think so because its by accidentdate year.

Doesnt AccidentDateTime need to be selected in the first CTE to do operations on it in the second CTE?  I see where your doing a Year(AccidentDateTime) in the second CTE.


Also can you show me how I can run a select of the result from the CTE's without running the update?
0
 
Ioannis ParaskevopoulosCommented:
Hi, sorry for the late reply.

Unfortunately ctes are limited to work only for the next statement after their declaration, so you will need to declare the first twice:

FirstCTE:
;With FirstCTE AS
(
SELECT claimnumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident]
)
SELECT * FROM FirstCTE

Open in new window


SecondCTE:
;With FirstCTE AS
(
SELECT claimnumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident]
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT * FROM SecondCTE

Open in new window


Final Result:
;With FirstCTE AS
(
SELECT claimnumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident]
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT *
FROM    [Accident].[tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber

Open in new window


The UPDATE statement i posted before had the purpose of fixing the claim number for accidents where the claim number was not in sync with the accident year.
0
 
Steve HougomDeveloper IIAuthor Commented:
Im getting this error for some reason.

error
0
 
Ioannis ParaskevopoulosCommented:
Yes... Indeed. It tries to get column AccidentDateTime from FirstCTE. Please add this column after claimnumber in FirstCTE declaration, and accept my appologies for being careless.

Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Now im getting this syntax error.

error
0
 
Ioannis ParaskevopoulosCommented:
This time is not my fault :P. You forgot a comma after AccidentDateTime.
0
 
Steve HougomDeveloper IIAuthor Commented:
Ok so I got the results back. And I agree that the new code we have will work once these rows are fixed.   HOWEVER.  I cant have it update legit claim numbers to new ones.  The business would never allow that because they are actually legit.  Its only the ones that have Year(accidentdatetime) != substring(claimnumber,2,2) e.g 12/21/2012 vs 130098

result
0
 
Ioannis ParaskevopoulosCommented:
Try this:

;With FirstCTE AS
(
SELECT claimnumber,
             AccidentDateTime,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident]
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT *
FROM    [Accident].[tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber
WHERE  LEFT(NewClaimNumber,2) <> RIGHT(CONVERT(VARCHAR,YEAR(A.AccidentDateTime)),2)

Open in new window

0
 
Steve HougomDeveloper IIAuthor Commented:
It works if you run the select against the accident table directly but not with cte.  See results below.

in sql db
actual
with cte 0 rows returned.
cte result
0
 
Ioannis ParaskevopoulosCommented:
That is correct, if we check the new claim number which is supposed to be correct with the accident dates then we will get 0 rows. In the WHERE clause replaise NewClaimNumber with A.claimnumber
0
 
Steve HougomDeveloper IIAuthor Commented:
This worked.  " In the WHERE clause replaise NewClaimNumber with A.claimnumber"


However.

What about duplicates though?  Ive already got these claimnumbers spoken for.

duplicates
0
 
Ioannis ParaskevopoulosCommented:
Ok..............Let's try once more....


;With ZeroCTE AS
(
    SELECT YEAR(AccidentDateTime) AS AccidentYear ,
                 MAX(claimnumber) AS MaxYearClaimNumber
    FROM   [Accident].[tbAccident]
    GROUP BY YEAR(AccidentDateTime) 
),FirstCTE AS
(
SELECT claimnumber,
             AccidentDateTime,
             MaxYearClaimNumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident] A
           JOIN ZeroCTE Z ON YEAR(AccidentDateTime) = MaxYearClaimNumber
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum + MaxYearClaimNumber),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT *
FROM    [Accident].[tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber
WHERE  LEFT(A.ClaimNumber,2) <> RIGHT(CONVERT(VARCHAR,YEAR(A.AccidentDateTime)),2)
                                            

Open in new window


What i did was added a new CTE (ZeroCTE) which calculates the max claim number for each year. Then in the SecondCTE i feed this number to the row number. This will make new entries to get new numbers.

Let me know how it goes.

Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
No rows returned.  

no data
0
 
Steve HougomDeveloper IIAuthor Commented:
First CTE returns the following.

first cte
0
 
Steve HougomDeveloper IIAuthor Commented:
On the topic of max claimnumber per year.  Not sure how the following messes up the firstcte for 2013.

max clm
0
 
Ioannis ParaskevopoulosCommented:
Ok... Try no. 8286786....

;With ZeroCTE AS
(
    SELECT YEAR(AccidentDateTime) AS AccidentYear ,
                 MAX(claimnumber) AS MaxYearClaimNumber
    FROM   [Accident].[tbAccident]
    WHERE  LEFT(ClaimNumber,2) <> RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
    GROUP BY YEAR(AccidentDateTime) 
),FirstCTE AS
(
SELECT claimnumber,
             AccidentDateTime,
             MaxYearClaimNumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [Accident].[tbAccident] A
           JOIN ZeroCTE Z ON YEAR(AccidentDateTime) = MaxYearClaimNumber
),
SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum + MaxYearClaimNumber),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT *
FROM    [Accident].[tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber
WHERE  LEFT(A.ClaimNumber,2) <> RIGHT(CONVERT(VARCHAR,YEAR(A.AccidentDateTime)),2)

Open in new window


Now i added a where clause to ZeroCTE in order to get the max of only the valid claimnumbers.
0
 
Steve HougomDeveloper IIAuthor Commented:
first cte

result
main query after where added to first cte

no rows
0
 
Ioannis ParaskevopoulosCommented:
Is it possible to go to SQLFiddle.com and add a sample of data? I am really running out of ideas trying to figure the best way to deal with this.

If you can add some sample over there (or even upload an excel) then i could defenetely help you create the correct query.

Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Im working on a dump from ssis.  Back soon I hope.  Thanks
0
 
Steve HougomDeveloper IIAuthor Commented:
data attached.
accident.xlsx
0
 
Ioannis ParaskevopoulosCommented:
Ok, got it... Be back to you shortly.
0
 
Ioannis ParaskevopoulosCommented:
That was quick:

;With ZeroCTE AS
(
    SELECT YEAR(AccidentDateTime) AS AccidentYear ,
                 MAX(claimnumber) AS MaxYearClaimNumber
    FROM   [tbAccident]
    WHERE  LEFT(ClaimNumber,2) = RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
    GROUP BY YEAR(AccidentDateTime) 
)
,FirstCTE AS
(
SELECT claimnumber,
             AccidentDateTime,
             MaxYearClaimNumber,
             ROW_NUMBER() OVER (PARTITION BY YEAR(AccidentDateTime) ORDER BY claimnumber) AS RowNum
FROM [tbAccident] A
           JOIN ZeroCTE Z ON YEAR(AccidentDateTime) = AccidentYear
)
,SecondCTE AS
(
SELECT claimnumber,
             RIGHT(CONVERT(VARCHAR,YEAR(AccidentDateTime)),2)
             + 
             RIGHT('0000' + CONVERT(VARCHAR,RowNum + MaxYearClaimNumber),4) AS NewClaimNumber
FROM   FirstCTE
)
SELECT *
FROM    [tbAccident] A
               JOIN SecondCTE S ON A.claimnumber = S.claimnumber
WHERE  LEFT(A.ClaimNumber,2) <> RIGHT(CONVERT(VARCHAR,YEAR(A.AccidentDateTime)),2)

Open in new window


Giannis
0
 
Steve HougomDeveloper IIAuthor Commented:
Thats looking better.  Now I just have to get up the courage to run the update and get back to running the updated .net code again.  Thanks!  Will report back if needed.  I will have to look at the sql closer to see what you changed.  The CTE is much better than temp tables.  Pretty neat.

result
0
 
Steve HougomDeveloper IIAuthor Commented:
Looking good.

Im using this.

 public int GetNextClaimNumber(int accidentDateYear)
        {
            //get the accident's year
            int year = accidentDateYear;
            //get the last accident number (you do not need the whole row, as you actually do not do anything with it
            var lastClaimNumber = _context.Accidents.Where(x => System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", x.AccidentDateTime) == year).OrderByDescending(c => c.ClaimNumber).FirstOrDefault().ClaimNumber;

            //the above will either return the last number for the year in question (First) or null (OrDefault), so there is only point to check if it is null, so it gets initiallized. This practically means that it is the very first record of a single year.
            if (lastClaimNumber == 0)
            {
                //I believe the way i present it here is simpler. ToString can format DateTime, and if we request "yy" then we get the two digit year representation (14 for 2014), and Concat can save us some characters of code..., though i guess the changes i did here could add only in readability than anything else.
                return Convert.ToInt32(string.Concat(DateTime.Now.ToString("yy"), "0001"));
            }
            else
            {
                return ++lastClaimNumber;
            }
        }

Open in new window


success
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 23
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now