Check if a date is between two other dates

[ashrafalzyoud]

Thx for suppourt and help

I have a tracker called "report_bill" id (1) and it has two Date attributes bill_from and bill_until. and sum_bills

I have a tracker called "payment" id (2) and it has date_bill and payment value

I want to write a method that checks and finds issue from tracker payment (id:2) include the date_bill between the two dates mentioned above.

Bill_from = @issue.custom_field_value(1)

Bill_until = @issue.custom_field_value(2)

Bill_date = @issue.custom_field_value(3)

Payment=@issue.custom_field_value(4).to_f

Sum_billa=@issue.custom_field_value(5).to_f

if tracker_id == 1 Sum = project.issues.includes(:custom_values).where(tracker_id:2 ; custom_field_3 , value between (1,2)).to_a.sum {|issue|issue.custom_field_value(4).to_f} end

[ashrafalzyoud]

@picman *****the format date in mysql its different in redmine and i cant match it like example cfs[1] its mysql 20024-06-13 cfs[1] its redmine 13/6/2024

---

how i can find issues between tow date

[picman]

from = Time.new(2024, 6, 13).strftime('%Y-%m-%d')
to = Time.new(2024, 6, 20).strftime('%Y-%m-%d')
issues = Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 92, customized_type: 'Issue' })
              .where(['custom_values.value BETWEEN ? AND ?', from, to])
Rails.logger.info ">>> Found issues are #{issues.map{ |i| "##{i.id}" }.join(',')}"

[ashrafalzyoud]

thx alot for your answer how i cant put custom field daate from = the date in my issue example cfs[1] from = @issue_custom_field_value(1).strftime('%Y-%m-%d') or from = @issue.start_date).strftime('%Y-%m-%d')

[picman]

from = @issue.start_date.strftime('%Y-%m-%d')

Custom field's value is a text.

[ashrafalzyoud]

if custom_field_value type date its text???

[picman]

Yes, there in the database it's stored as a text. The command works fine:

>> Issue.first.start_date.strftime('%Y-%m-%d')
=> "2010-02-25"

[ashrafalzyoud]

thx mr @picman about your support and good answer my code now like this

if tracker_id == 230
from = @issue.start_date.strftime('%Y-%m-%d')
to =  @issue.due_date.strftime('%Y-%m-%d')

sum = Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 420, customized_type: 'Issue' })
              .where(['custom_values.value BETWEEN ? AND ?', from, to])
              .where(tracker_id: 128,project_id: self.project_id)
              .and(Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 10, value: "A" })
              .where(tracker_id: 128,project_id: self.project_id))
              .all.sum{|c| c.custom_field_value(292).to_f}.to_f.round(3)

@issue.custom_field_values = { '456' => sum }
end

im added tow part 1-

              .and(Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 10, value: "A"})
              .where(tracker_id: 128,project_id: self.project_id))

2- .all.sum{|c| c.custom_field_value(292).to_f}.to_f.round(3)

i need put tow condition 1- for between dates 2- if issue have custom field(10) value A AND THen calcuate the paymen (float) for the issues have date between it and have value A

are my code correct,,because all sum 0 not get the correct total payment

[ashrafalzyoud]

im try this code plz fix it because the output not correct see the pic plz im added 3 filiter tracker date between custom value type (a) to collect payment

if project.present?
if tracker_id == 230
from = @issue.start_date.strftime('%Y-%m-%d')
to =   @issue.due_date.strftime('%Y-%m-%d')

 @list1 = Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 10, value: "19" })
              .where(tracker_id: 128,project_id: self.project_id)
              .pluck(:id)

 @float_value1 = CustomValue.where("customized_id IN (#{@list1.join(',').to_s}) AND custom_field_id = '292' ").pluck(:value)

 @list2 = Issue.joins(:custom_values)
              .where(custom_values: { custom_field_id: 420})
              .where(['custom_values.value BETWEEN ? AND ?', from, to])
              .where(tracker_id: 128,project_id: self.project_id)
              .pluck(:id)

@float_value2 = CustomValue.where("customized_id IN (#{@list2.join(',').to_s}) AND custom_field_id = '292' ").pluck(:value)
@float_value3 = @float_value2 & @float_value1

     @issue.custom_field_values = { '466' =>  @float_value3.uniq.size.to_i.to_s}
     @issue.custom_field_values = { '456' =>  @float_value3.inject { |sum, n| sum.to_f + n.to_f }}
end

end