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.
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:
When the user clicks on the spyglass it invokes the create method in the controller.
The accident date section in the view looks like this.
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.
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.
If more code or model info is needed I can provide that so I can help you help me figure this out.
Thanks.
The rendered page from the view looks like this.
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>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); The accident date section in the view looks like this.
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>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;
}
}If more code or model info is needed I can provide that so I can help you help me figure this out.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great help. Turned out to be super easy. Expert answered the original question.
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
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
ASKER
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.
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.
In the model/vew the accident date and claim number are declared as below.
_accidentRepository.SaveChanges();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.
In the model/vew the accident date and claim number are declared as below.
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:
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
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)))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
ASKER
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
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
ASKER
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.
I think you are close:
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.SqlServ
Let me know how it goes ;)
Giannis
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;
}
}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.SqlServ
Let me know how it goes ;)
Giannis
In order to fix the messed up records i would suggest running an update query. The following may help:
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
;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.claimnumberBefore 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
ASKER
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.System.Data.Objects.SqlClient.SqlFunctions.DatePart("year", x.AccidentDateTime) == year)
Did you run the Update script?
ASKER
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.
'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.
ASKER
Looks like I have 2008.
ASKER
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.
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.
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();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.
I am really sorry... It id ROW_NUMBER. Forgot the underscore.
I suggest running this query first. Then you should be good to go
I suggest running this query first. Then you should be good to go
ASKER
Just curious. Is the numbering below correct? I see 14's with the same row number set as the 12's.
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?
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?
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:
SecondCTE:
Final Result:
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.
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 FirstCTESecondCTE:
;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 SecondCTEFinal 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.claimnumberThe 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.
ASKER
Im getting this error for some reason.
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
Giannis
ASKER
Now im getting this syntax error.
This time is not my fault :P. You forgot a comma after AccidentDateTime.
ASKER
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)
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)ASKER
It works if you run the select against the accident table directly but not with cte. See results below.
in sql db
with cte 0 rows returned.
in sql db
with cte 0 rows returned.
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
ASKER
This worked. " In the WHERE clause replaise NewClaimNumber with A.claimnumber"
However.
What about duplicates though? Ive already got these claimnumbers spoken for.
However.
What about duplicates though? Ive already got these claimnumbers spoken for.
Ok..............Let's try once more....
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
;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)
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
ASKER
No rows returned.
ASKER
First CTE returns the following.
ASKER
On the topic of max claimnumber per year. Not sure how the following messes up the firstcte for 2013.
Ok... Try no. 8286786....
Now i added a where clause to ZeroCTE in order to get the max of only the valid claimnumbers.
;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)Now i added a where clause to ZeroCTE in order to get the max of only the valid claimnumbers.
ASKER
first cte
main query after where added to first cte
main query after where added to first cte
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
If you can add some sample over there (or even upload an excel) then i could defenetely help you create the correct query.
Giannis
ASKER
Im working on a dump from ssis. Back soon I hope. Thanks
ASKER
data attached.
accident.xlsx
accident.xlsx
Ok, got it... Be back to you shortly.
That was quick:
Giannis
;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)Giannis
ASKER
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.
ASKER
Looking good.
Im using this.
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;
}
}
ASKER
But as far as passing in the value my gosh it was way easier than I thought because of entity framework.
Thanks