[Development log ⑪] Calculate the total number of days spent from the date of paid holidays to the present-from the number of remaining days

About the premise

Nice to meet you, my name is Irifune and I go to a programming school. This article is used for my own output by writing a record of the development of personal apps, which is a school task. If anyone has read it, I would be grateful if you could give us feedback.

We will develop a "paid leave management tool". For specifications, see Past Articles.

The app will be deployed, but it is not provided as a service. Please understand that this is just a part of self-study. Then go to the main subject.

Contents of this implementation

Up to the previous time, we were able to calculate the grant date (the date on which paid leave is granted 6 months after the hire date) and the number of years of service based on the employee's hire date. Today, let me calculate the total number of digestion days. Specifically, consider the following procedure.

――What is the total number of digestion days? ――What is the total number of digestion days handled this time? --Search for the range to be totaled --Total search results --Display the total result in the view --OK if you can check it with your local browser

What is the total number of digestion days?

First of all, the paid leave that you receive every year is valid for two years after it is granted. When digesting, it is also important to digest the oldest ones first.

For example, if a person who joined the company on April 1, 2018 digests three days of paid leave in two steps, the flow will be as follows.

In other words, the total number of paid holidays as of May 17, 2020 can be calculated by searching the table for the following two records.

--Total from the latest grant date to the present (2019/10/01 ~ 2020/5/17) --Total from the previous grant date to the latest grant date (2018/10/1 ~ 2019/10/1)

However, if you go with this formula, you need to separately calculate the paid holidays that disappear undigested on October 01, 2020.

What is the total number of digestion days handled this time?

This time, the number of days calculation that you want to finally implement in the application is as follows.

① Total number of days granted for the past two years ② Total number of digestion days from the latest grant date to the present ③ Total number of digestion days from the previous grant date to the latest grant date ④ Number of days remaining after subtracting ② and ③ from ①

I wrote it for a long time, but I wrote it as my own memorandum. Therefore, the way of thinking may change in the future. Please think of it as an event in Irifune's brain at the time of writing the article. So, this time we implemented "② Total number of digestion days from the latest grant date to the present", so we will introduce this from the following.

Search for the range to be totaled

Until the last time, the table was created only with the records of employees, but from this time, the records of paid leave will be included in the calculation. Employees are stored in the employee table and paid leave is stored in the holiday table, so edit them from the controller.

employees_controller.rb

class EmployeesController < ApplicationController
  def index
    @employees = Employee.where(branch_id: params[:branch_id]).includes(:holidays)
  end
~ Omitted ~
end

In the latter half, the records of holiday with employee_id are also acquired by the includes method. The includes method appeared when I learned the "N + 1 problem" in the curriculum. In that case, I learned that when retrieving many records with a foreign key from a table with a one-to-many relationship, the record with the primary key is used to look ahead. This time, I got the record of the foreign key linked to the record with the primary key, so the order is reversed, but I was able to use it safely.

Referenced article "Get and display one-to-many table data with Rails" @bitarx

Next, in the model file, define a method to perform the search "from the latest grant date to the present".

models/employee.rb

class Employee < ApplicationRecord
~ Omitted ~
  def range_to_add_or_delete
    grant_day = hire_date >> 6
    month = grant_day.month
    day = grant_day.mday
    year = Date.today.year
    grant_date_this_year = Time.local(year, month, day)

    if grant_date_this_year > Date.today
      last_year = year - 1
      grant_date_this_year = Time.local(last_year, month, day)
    else
      grant_date_this_year
    end

    holidays.where(created_at: grant_date_this_year..Date.tomorrow)
end

First, grant_day at the beginning calculates the statutory grant date (6 months after joining the company). I use the ".month" and ".mday" methods to extract the month and day numbers and store them in variables. For the year, I prepare today's date with "Date.today" and execute the ".year" method to extract the year number. Finally, combine the three numbers with "Time.local" to complete the latest grant date (grant_date_this_year).

Referenced article "[Introduction to Ruby] 14. Handling Dates and Times (covering all patterns)" @prgseek

Next, in the IF statement, the year is processed according to the conditions to determine the final most recent grant date. The condition is whether the latest grant date (grant_date_this_year) created earlier is larger than the current date. The reason for doing this is that if the statutory grant date is December, the result of the latest statutory grant date will be December 1, 2020, which is a future date. Therefore, if the most recent grant date once completed is larger than the current date, execute -1 from the year and recreate the latest grant date with a new variable called "last_year". After else, it cannot be determined at this stage whether it is necessary. I'm sorry.

In the intermediate result view, the latest grant date of each employee is displayed in the digestion days column. Master Shouta, who joined the company in April, does not have to have a future date. Was good~! !!

Let's return to the code description. Search for created_at in the range from the latest grant date completed in the last sentence to the present, and search for the required record.

The article I referred to "How to write a date / time range search simply with ActiveRecord" @ hachi8833

Sum up search results

Since I was able to search within the specified range, I will calculate the total based on this. Write another method that lets the model file calculate the total. Since this time it is under development, we decided to calculate the total number of grant dates using the same method. As mentioned above, there is another method for calculating the total grant date, but since there are other methods that must be implemented for the total grant date, this time we will leave it the same for view confirmation.

models/employee.rb

class Employee < ApplicationRecord
~ Omitted ~
  def total_delete_day
    total_delete_day = range_to_add_or_delete.sum(:delete_day)
  end
  def total_add_day
    total_add_day = range_to_add_or_delete.sum(:add_day)
  end
  def calculate_remaining_days
    a = total_delete_day
    b = total_add_day
    b - a
  end
end

The argument of the ".sum" method is the column name for which you want to sum. Referenced article "[Rails] Find the total value of columns!" @tomokichi_ruby

Next, calculate_remaining_days is defined to calculate the number of remaining days. Originally, if the result of subtracting the total of the grant date and the total of the digestion date is negative, it should be conditional so that an error will occur, but it is also for view confirmation and it is simply subtracted.

Show the total result in the view

haml:branches/index.html.haml

.main
  =render 'branches/mainheader'
  .main__body
Click "Name" to edit employee data, and delete it on the far right.
    %table 
      %tr
~Omission~
        %th{id: "short"}
Number of days granted
        %th{id: "short"}
Digestion days
        %th{id: "short"}
Remaining days
        - @employees.each do |employee|
          %tr
~ Omitted ~
            %th{id: "short"}
              = employee.total_add_day
            %th{id: "short"}
              = employee.total_delete_day
            %th{id: "short"}
              = employee.calculate_remaining_days
~ Omitted ~

I reflected the method defined earlier in "index.html.haml". The result of the view is as follows. I was able to calculate it safely.

Today's stack

I'm getting used to posting articles on Qiita. I feel that there are a lot of sentences for the content, but if I think that I have investigated or stumbled, and if it helps the same beginner, is the explanation stupid and polite? I try to think. However, there are many articles that can be used as a reference for beginners by writing them concisely, such as the articles that I referred to, so I would like to be aware of the conciseness. that's all.