Need help for a Groovy Script to calculate costs based on the dates

[venkatesh_rajput]

I have two date custom fields, start date and end date.

Third is the text field where I will capture the cost.

Now in the fourth field, I want to see the cost in a table form for 2023 and 2024.

Suppose the start date is Nov 2023 and the end date is Jan 2024

and the value for the third text field is $6k.

Now in the fourth field, I want a table where there will be two headings 2023 and 2024

where the value under 2023 will be 4k and value under 2024 will be 2k.

So basically we are using the date field to divide the cost on a monthly basis and then showing the cost divided within the years.

[Hauke Bruno Wollentin · Accepted solution]

Could you paste the script you're having right now?

Since you mentioned Groovy I assume you are using ScriptRunner, so a Scripted Field should do the trick pretty good :) 

The DatePicker field in Jira Server internally returns a java.sql.Timestamp object with provides a `toLocalDateTime()` method.

With the LocalDateTime object you can start looking at your end date, do the math and deduct until you reach your start date or so.

[venkatesh_rajput]

---

import com.atlassian.jira.component.ComponentAccessor

import com.atlassian.jira.event.type.EventDispatchOption

def cfm = ComponentAccessor.customFieldManager

def issueManager = ComponentAccessor.issueManager

def startDate = getFieldById("customfield_number1")

def endDate = getFieldById("customfield_number2")

def issueStartDate = startDate.getValue() as Date

def issueEndDate = endDate.getValue() as Date

def cost = getFieldById("customfield_number3")

def duration = issueEndDate - issueStartDate

def durationValue = duration.toInteger()

def month = durationValue/30

---

This is what I have been able to write, but the problem is how will I calculate the cost for 2023 and 2024 separately.

Currently I have been able to figure out how can I calculate the days in between and I have divided it by 30 to get the value in months but to separate the values for different years is a task.

I can divide the cost with the value of month here, but to separate it between the years is a big task here.

[Hauke Bruno Wollentin]

Disclaimer: I'm not a programmer :) 

But my approach would be to convert both timestamps to LocalDateTime and start calculating "down" from the Target Date until the Start Date is reached.

With `.getYear()` you are able to know the year so you could create a list or so for every year to save the amount of months in it (just incrementing it if `getYear()` is still the current year your variable points to).

I tested a bit and came up with the following:

import com.atlassian.jira.component.ComponentAccessor

def cfm = ComponentAccessor.customFieldManager

def issueManager = ComponentAccessor.issueManager

def issue = issueManager.getIssueObject("HAUKE-7")

def cfStartDate = cfm.getCustomFieldObjectsByName("Start date").first()

def cfEndDate = cfm.getCustomFieldObjectsByName("End date").first()

def startDate = issue.getCustomFieldValue(cfStartDate)

def endDate = issue.getCustomFieldValue(cfEndDate)

startDate = startDate.toLocalDateTime()

endDate = endDate.toLocalDateTime()

def currentDate = startDate

Map results = [:]

while(currentDate.isBefore(endDate)) {

  results.merge(currentDate.getYear(), 1, Integer::sum)

  currentDate = currentDate.plusMonths(1)

}

return results

While results will finally hold a key for each year in the difference of both DatePicker fields and a value of how many months are included in the timeframe.

Those information than can be processed.

Edit: Sorry, while typing things down I change my idea to calculate down from End date to Start Date; the code actually does the opposite 🙃

[Hauke Bruno Wollentin]

Finally you could end up in using something like this (again, I'm not a programmer so I'm pretty sure this could be done better, more efficient and prettier) which you could throw in your ScriptRunners Scripted Field:

import java.lang.Integer

import com.atlassian.jira.component.ComponentAccessor

def cfm = ComponentAccessor.customFieldManager

def issueManager = ComponentAccessor.issueManager

def issue = issueManager.getIssueObject("HAUKE-7")

def cfStartDate = cfm.getCustomFieldObjectsByName("Start date").first()
def cfEndDate = cfm.getCustomFieldObjectsByName("End Date").first()


def startDate = issue.getCustomFieldValue(cfStartDate)
def endDate = issue.getCustomFieldValue(cfEndDate)

startDate = startDate.toLocalDateTime()
endDate = endDate.toLocalDateTime()

def currentDate = startDate

Map results = [:]

while(currentDate.isBefore(endDate)) {

 results.merge(currentDate.getYear(), 1, Integer::sum)

 currentDate = currentDate.plusMonths(1)

}

def totalMonths = results.values().sum()

def totalMoney = 1337.75 // or value of another custom field

float moneyPerMonth = totalMoney / totalMonths

def finalTextForNewField = ""

results.each { year, months ->

  double sumForMonth = months * moneyPerMonth

  finalTextForNewField += "Sum for ${year}: ${sumForMonth.round(2)}\r\n"

}

return finalTextForNewField

[venkatesh_rajput]

Hi, I am getting this error

import java.lang.Integer

import com.atlassian.jira.component.ComponentAccessor

def cfm = ComponentAccessor.customFieldManager

def issueManager = ComponentAccessor.issueManager

def issue = issueManager.getIssueObject("NIV-23")

def cfStartDate = cfm.getCustomFieldObjectsByName("Start Date").first()

def cfEndDate = cfm.getCustomFieldObjectsByName("End Date").first()

def startDate = issue.getCustomFieldValue(cfStartDate)

def endDate = issue.getCustomFieldValue(cfEndDate)

startDate = startDate.toLocalDateTime()

endDate = endDate.toLocalDateTime()

def currentDate = startDate

Map results = [:]

while(currentDate.isBefore(endDate)) {

 results.merge(currentDate.getYear(), 1, Integer:sum)

 currentDate = currentDate.plusMonths(1)

}

def totalMonths = results.values().sum()

def totalMoney = 1337.75 // or value of another custom field

float moneyPerMonth = totalMoney / totalMonths

def finalTextForNewField = ""

results.each { year, months ->

  double sumForMonth = months * moneyPerMonth

  finalTextForNewField += "Sum for ${year}: ${sumForMonth.round(2)}\r\n"

}

return finalTextForNewField

Was getting error for
results.merge(currentDate.getYear(), 1, Integer :: sum)

so I made it results.merge(currentDate.getYear(), 1, Integer:sum)

But i am getting this error now

Error

groovy.lang.MissingPropertyException: No such property: sum for class: Script13705 at Script13705.run(Script13705.groovy:27)

An error occurred, the field will have no value

[venkatesh_rajput]

Also is it possible not to keep the script issue specific? When I remove "def issue" line I get lots of errors

[venkatesh_rajput]

Hi, sorry I forgot to update you. Thank you very much for your help below field worked for me

import java.lang.Integer
import com.atlassian.jira.component.ComponentAccessor

def cfm = ComponentAccessor.customFieldManager
def issueManager = ComponentAccessor.issueManager
def issue = issueManager.getIssueObject("TEST-8")

def cfStartDate = cfm.getCustomFieldObjectsByName("Start Date").first()
def cfEndDate = cfm.getCustomFieldObjectsByName("End Date").first()

def startDate = issue.getCustomFieldValue(cfStartDate)
def endDate = issue.getCustomFieldValue(cfEndDate)

startDate = startDate.toLocalDateTime()
endDate = endDate.toLocalDateTime()

def currentDate = startDate

Map<Integer, Integer> results = [:] // Use Map<Integer, Integer> to store year and sum

while (currentDate.isBefore(endDate)) {
results.merge(currentDate.getYear(), 1, { oldVal, newVal -> oldVal + newVal }) // Increment sum
currentDate = currentDate.plusMonths(1)
}

def totalMonths = results.values().sum()

def totalMoney = 1337.75 // or value of another custom field

float moneyPerMonth = totalMoney / totalMonths

def finalTextForNewField = ""

results.each { year, months ->
double sumForMonth = months * moneyPerMonth
finalTextForNewField += "Sum for ${year}: ${sumForMonth.round(2)}\r\n"
}

return finalTextForNewField

Thank you very much, without you it would have never been possible. 

[Hauke Bruno Wollentin]

Awesome :) 

For your other question: In the Scripted Field context you will have some default bindings (which you can see by clicking the blue question mark near the code input field).

issue is one of those which represents the current issue a user is viewing in this case.

So outside the Console and testing you can just remove the `def issue = ...` line as well as the issueManager stuff :)