Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello, I am currently working on an app which lets users create project ideas and send them for review to their supervisors. Project numbers of said projects need to be formatted in a certain way, first the current year, and then, separated by the dot, the continuous number showing the order in which the project has been added, for example 2021.001, 2021.003, 2022.004 and so on. Currently in the Project table I have (among other, not relevant to this case) columns: ProjectNumber (Int) and FullProjectNumber (String). When new project is created, in my code I check if there are any records in the table. If there are none, I create a project with ProjectNumber of value 0, if there are some, I am incremnting the highest project number. Then, to get a FullProjectNumber, I concatenate the year and format the string.

What I have tried:

My code:

C#
public async Task<Guid> Handle(CreateProjectCommand command, CancellationToken cancellationToken)
          {
              DateTime myDateTime = DateTime.Now;
              string year = myDateTime.Year.ToString();




              var lastProjectNumber = _context.Projects.OrderByDescending(m => m.ProjectNumber).FirstOrDefault();


              var Project = new Project()
              {
                  ProjectName = command.ProjectName,
                  ContractStartDate = command.ContractStartDate,
                  ContractEndDate = command.ContractEndDate,
                  NameOfSuplier = command.NameOfSuplier,
                  ValueImprovementTypeId = command.ValueImprovementTypeId,
                  OverallImprovementTypeId = command.OverallImprovementTypeId,
                  LegalEntity = command.LegalEntity,
                  AdditionalEntities = command.AdditionalEntities,
                  GPCategory = command.GPCategory,
                  GPSubCategory = command.GPSubCategory,
                  EBITDA = command.EBITDA,
                  ProjectResponsible = command.ProjectResponsible,
                  ProjectNumber = command.ProjectNumber,
                  ProjectStatus = Guid.Parse("05C2F392-8B69-4915-A166-C4418889F9E8")
              };

              _context.Projects.Add(Project);

              if (lastProjectNumber == null)
              {
                  Project.ProjectNumber = command.ProjectNumber = 1;

              }
              else if (lastProjectNumber.ProjectNumber == 0)
              {
                  Project.ProjectNumber = command.ProjectNumber = 1;

              }
              else
              {
                  Project.ProjectNumber = lastProjectNumber.ProjectNumber++;

              }


              Project.FullProjectNumber = string.Join(".", year, Project.ProjectNumber.ToString().PadLeft(3, '0'));



              await _context.SaveChanges();
              return Project.ProjectId;


The problem is, the project numbers I am getting are not in the right order.
This what I am currently getting:
https://postimg.cc/MMsHrKmt
And this is what I need to achieve:
https://postimg.cc/xq4qv8C1
Posted
Updated 5-Dec-21 22:21pm
v2
Comments
[no name] 5-Dec-21 12:09pm    
You should use an IDENTITY column for the project #, and let the DB generate it. You don't need to store the "formatted" project #; you can easily generate it at "report / display" time if you just store the year in the table.

1 solution

0) You don't need to store the "full project number" string. You can build it when you query the database to retrieve the desired data.

1) You shouldn't create a new project in the database unless certain minimal criteria have been met. This will eliminate the arbitrary need to have a project number of 0.

2) When you submit a project to the database, You can find the last project number by using a query that uses the MAX function and a GROUP BY clause. Group by the year of the date the projects have been submitted, something like this:

SQL
-- get the last project number in the table. The possible 
-- values should be either null, or a numeric value.
DECLARE @nextProject int = (
                               SELECT max(projectnumber)
                               FROM projects
                               WHERE YEAR(projectdate) = @projectYear
                               GROUP BY YEAR(projectdate)
                           );
-- increment the value to get a new project number
SET @nextProject = ISNULL(@nextProject, 0) + 1;


The reason you have to do it this way is because the project number resets to zero at the beginning of every year (at least, that's what I assume you're going for). This means you can't make the projectnumber column an IDENTITY column.

3) After determining the next available project number, you can store the project data in the table

SQL
INSERT INTO projects (projectnumber, projectdate, [other columns...])
VALUES
(
    @nextProject,
    GETDATE(),
    [other column data...]
);


4) To retrieve projects for a given year, you might do something like this:

SQL
SELECT projectnumber, 
       projectdate, 
       FORMAT(YEAR(projectdate), '#000#') + FORMAT(projectnumber,'00#') AS projnumbstr,
       [other columns...]
FROM   projects
WHERE  YEAR(projectdate) = @projYear;
 
Share this answer
 
v2
Comments
[no name] 6-Dec-21 12:16pm    
Re IDENTITY: "example 2021.001, 2021.003, 2022.004 and so on. "
#realJSOP 7-Dec-21 5:50am    
I think I may have inadvertently made it more useful. His question needs to be clarified as to the nature of the project numbers.

I suppose a literal reading of his question implies that the the new year does not cause a reset of the actual project number back to 1 (based on his sample project numbers), but the fact that he includes (what I assume to be) the year in the project number string indicates that the intent is in fact to do that reset, and that's the basis of my answer. I'm guessing we'll never know, because many of the guys posting questions don't indicate that they've seen answers.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900