Help with time calculation

[lespaul]

Hello again!

Here's my current problem. In the Jury Management system I/we have been working one, one of the processes figures out how many hours each juror has worked and how much they are owed. However, public employees (those that work for the State, city, etc), only get paid for hours at the court OUTSIDE of their normal working hours. For example, a juror is a public employee and has regular working hours of 12:00 pm until 9:00 pm. If they served at the court the following times:

day 1 9:30 am 11:30 am (paid for all hours)
day 2 2:30 pm 4:30 pm (paid for no hours)
day 3 9:30 am 4:30 pm (paid from 9:30 - 12:00)

Does anyone have an idea on the best way to calculate the number of hours to be paid? So I end up with:

Day 1 2 hours
day 2 0 hours
day 3 2.5 hours

I've been thinking along the lines of an if or case statement, but can't quite work out all the possibilites. Any assistance would be appreciated!

Thanks
Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

I think this will do it for you:

const
  PE_STARTTIME = '12:00 pm';
  PE_STOPTIME = '9:00 pm';

function CalcPublicEmployeeHours( Start,Stop:string ): double;
var
  tStart,tStop:TDateTime;
  tPEStart,tPEStop: TDateTime;
  nPaidTime: double;
begin
  tPEStart := StrToTime( PE_STARTTIME);
  tPEStop := StrToTime( PE_STOPTIME );
  tStart := StrToTime( Start );
  tStop := StrToTime( Stop );
  nPaidTime := 0;
  if tStart < tPEStart then
    if tStop > tPEStart then nPaidTime := nPaidTime +  (tPEStart - tStart)
    else nPaidTime := nPaidTime +  (tStop - tStart);
  if tStop > tPEStop then
    if tStart < tPEStop then nPaidTime := nPaidTime +  (tStop - tPEStop)
    else nPaidTime := nPaidTime +  (tStop - tStart);
  Result := nPaidTime * 24.0;
end;

procedure TfrmMain.Button1Click(Sender: TObject);
begin
  ShowMessage ( FloatToStr( CalcPublicEmployeeHours( '9:30 am' , '11:30 am' ) )
    + ' --- ' + FloatToStr( CalcPublicEmployeeHours( '2:30 pm' , '4:30 pm' ) )
    + ' --- ' + FloatToStr( CalcPublicEmployeeHours( '9:30 am' , '4:30 pm' ) ) );
end;

 

[lespaul]

Thanks zathras! You're posting put me on the right track. Here's what I ended up with:

function CalcPublicEmployee(StartTime, EndTime : TDateTime): double;
var
PEStartTime, PEEndTime : TDateTime;
PaidTime : double;
begin
  JMSData.qry2Temp.SQL.Clear;
  JMSData.qry2Temp.SQL.Add('SELECT * FROM JMPPEMPLE WHERE JURNUM = ' +
   JMSData.qryHoursCalc.FieldByName('JURNUM').AsString);
  JMSData.qry2Temp.Active := True;
  PEStartTime := IntToTime(JMSData.qry2Temp.FieldByName('REGTIMEIN').AsInteger);
  PEEndTime := IntToTime(JMSData.qry2Temp.FieldByName('REGTIMEOUT').AsInteger);
  if EndTime < PEStartTime then
    Result := HourSpan(StartTime, EndTime)
  else if (StartTime < PEStartTime) AND (EndTime < PEEndTime) then
    Result := HourSpan(StartTime, PEStartTime)
  else if (StartTime > PEStartTime) AND (EndTime > PEEndTime) then
    Result := HourSpan(PEEndTime, EndTime)
  else
    Result := 0;
end;

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zathras]

Glad to be able to help. If I move to NM, can you get me out of jury duty?

Are you sure you want to be using .AsInteger when retrieving the database values? I think if you do, you will lose the time portion of the TDateTime values. Perhaps you should consider using the .AsDateTime property instead.

 

[lespaul]

I am using the integer because we use an AS400 which doesn't have a date/time field. All the times are stored as integers in 24 hour format. I then I have an IntToTime function that converts the integer into a TDateTime. Trust me I wish we had a database date/time field and we didn't have to do it!

And if you want to get out of jury duty here, it's easy - IGNORE THE SUMMONS!!! Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!