unit VoucherProcessing;
interface
uses ComObj, Forms, Controls, IDGlobal, Dialogs, SysUtils, Variants,
DateUtils, Classes;
procedure ProcessVouchers();
implementation
uses JMSData_Module, FormatFunctions, ProperCaseFunction, MainMenu,
HoursProcessing, RoundNumber;
var
Save_Cursor:TCursor;
tripsmade : integer;
booPubEmp : boolean;
booOrientPanel : boolean;
const payrate = 5.15;
const milerate = 0.32;
procedure CreateTextFile(AList : array of const);
begin
//do stuff
end;
//process to determine which hours the Public Employee gets paid for
//if regular working hours are 8 - 5 and the juror serves from 2 - 6
//then the juror will get paid for 1 hour since the hours from 2 - 5
//are on public time only the hour from 5 - 6 is on the person's time
function CalcPublicEmployee(StartTime, EndTime : TDateTime): double;
var
PEStartTime, PEEndTime : TDateTime;
begin
JMSData.qryPEJurorHours.SQL.Clear;
JMSData.qryPEJurorHours.SQL.Add('SELECT * FROM JMPPEMPLE WHERE JURNUM = ' +
JMSData.qryHoursCalc.FieldByName('JURNUM').AsString);
JMSData.qryPEJurorHours.Active := True;
PEStartTime := IntToTime(JMSData.qryPEJurorHours.FieldByName('REGTIMEIN').AsInteger);
PEEndTime := IntToTime(JMSData.qryPEJurorHours.FieldByName('REGTIMEOUT').AsInteger);
if (JMSData.qryPEJurorHours.FieldByName('REGTIMEIN').AsInteger = 0) AND
(JMSData.qryPEJurorHours.FieldByName('REGTIMEOUT').AsInteger = 0) then
Result := 0
else if EndTime < PEStartTime then
Result := RoundN(HourSpan(StartTime, EndTime),1)
else if (StartTime < PEStartTime) AND (EndTime < PEEndTime) then
Result := RoundN(HourSpan(StartTime, PEStartTime),1)
else if (StartTime > PEStartTime) AND (EndTime > PEEndTime) then
Result := RoundN(HourSpan(PEEndTime, EndTime),1)
else
Result := 0;
JMSData.qryPEJurorHours.Active := False;
end;
//calculate the number of trips and the number of hours the juror worked during term
function GetTotalHours(strJurorNumber : string) : double;
var
daysworked, k, j : integer;
TotalHours : double;
HoursWorked : array of array of TDateTime;
begin
If JMSData.qryHoursCalc.FieldByName('DNPT').AsString = 'F' then
begin
with JMSData.qryTemp do
begin
SQL.Clear;
SQL.Add('SELECT * FROM JMPHOURS WHERE JURNUM = ' + strJurorNumber +
' ORDER BY SERVDAT, TIMETYPE');
Active := True;
While not EOF do
begin
If not ((FieldByName('SERVDAT').AsString =
convertdate(frmMainMenu.processDate)) AND
((FieldByName('TIMETYPE').AsString = 'TI')
OR (FieldByName('TIMETYPE').AsString = 'TO'))) then
begin
inc(daysworked);
end
else if not booOrientPanel then
begin
inc(daysworked);
end;
Next;
end;
//verify even number of records
If not odd(daysworked) then
begin
//tripsmade determines the mileage pay but the panel who served
//on orientation day needs extra slot for extra time entry
tripsmade := daysworked div 2;
if booOrientPanel then
begin
SetLength(HoursWorked, tripsmade + 1, 3);
daysworked := daysworked + 2;
end
else
SetLength(HoursWorked, tripsmade, 3);
First;
k := -1;
for j := 0 to (daysworked - 1) do
begin
If j mod 2 = 0 then
begin
inc(k);
Hoursworked[k , 0] := StrToDate(DateFormat(FieldByName('SERVDAT').AsString));
end;
if ((FieldByName('TIMETYPE').AsString = 'TI') or (FieldByName('TIMETYPE').AsString = 'OI')) then
Hoursworked[k, 1] := IntToTime(FieldByName('TIMEIO').AsInteger)
else if ((FieldByName('TIMETYPE').AsString = 'TO') or (FieldByName('TIMETYPE').AsString = 'OO')) then
Hoursworked[k, 2] := IntToTime(FieldByName('TIMEIO').AsInteger);
JMSData.qryTemp.Next;
end;
for j := 0 to ((daysworked div 2) - 1) do
begin
if not booPubEmp then
TotalHours := TotalHours + RoundN(HourSpan(HoursWorked[j, 1], HoursWorked[j, 2]),1)
else
TotalHours := TotalHours + CalcPublicEmployee(HoursWorked[j, 1], HoursWorked[j, 2]);
end;
end;
Result := TotalHours;
end;
end
else
Result := 0;
end;
//calculate total number of miles (RTM * tripsmade)
function GetTotalMiles() : integer;
begin
with JMSData.qryHoursCalc do
begin
If FieldByName('DNPM').AsString = 'F' then
begin
If booPubEmp then
begin
If FieldByName('RTM').AsInteger < 30 then
Result := 0
else
Result := FieldByName('RTM').AsInteger * tripsmade;
end
else
Result := FieldByName('RTM').AsInteger * tripsmade;
end
else
Result := 0;
end;
end;
//set boolean to determine if the panel the juror is on was called to serve
//on orientation day
function CheckOrientation(strPanelID : string) : boolean;
begin
With JMSData.qry2Temp do
begin
SQL.Clear;
SQL.Add('SELECT SESSION FROM JMPDLYPANL WHERE SERVDAT = ' + QuotedStr(convertdate(frmMainMenu.processDate)) + 'AND PANELID = ' +
QuotedStr(strPanelID));
Active := True;
If JMSData.qry2Temp.FieldByName('SESSION').AsString = 'AM' then
Result := True
else
Result := False;
end;
end;
//set boolean to determine if juror is public employee
function CheckPubEmployee(strJurorNumber : string) : boolean;
begin
If JMSData.qryHoursCalc.FieldByName('PUBEMP').AsString = 'T' then
begin
with JMSData.qryPEJurorHours do
begin
SQL.Clear;
SQL.Add('SELECT * FROM JMPPEMPLE WHERE JURNUM = ' + strJurorNumber);
Active := True;
If (FieldByName('PARTTIME').AsString = 'F') AND (FieldByName('SUBSTITUTE').AsString = 'F') AND
(FieldByName('OTHER').AsString = 'F') then
result := True
else
result := False;
end;
end
else
result := False;
end;
//main processing function
procedure ProcessVouchers();
var
ProcessList : variant;
TextList : array of array of Variant; //Here's where I'm having problems
personcount, i : integer;
firstvoucher, lastvoucher : string;
excelapp, excelsht : Variant;
PaidJurorList : Tstrings;
begin
Save_Cursor := Screen.Cursor;
Screen.Cursor := crHourGlass;
try
//if query not empty then process
With JMSData.qryHoursCalc do
begin
If not IsEmpty then
begin
While not EOF do
begin
inc(personcount);
Next;
end;
ProcessList := VarArrayCreate([0, (personcount - 1), 0, 8], varOleStr);
SetLength(TextList, personcount, 8);
First;
For i := 0 to (personcount - 1) do
begin
booPubEmp := CheckPubEmployee(FieldByName('JURNUM').AsString);
booOrientPanel := CheckOrientation(copy(FieldByName('PANELID').AsString,1, 9));
ProcessList[i , 0] := ProperCase(FieldByName('FIRSTNAME').AsString +
' ' + FieldByName('LASTNAME').AsString) + ' (' + FieldByName('PANELID').AsString + ')';
ProcessList[i , 1] := ProperCase(FieldByName('STREET1').AsString);
ProcessList[i , 2] := ProperCase(FieldByName('CITY').AsString) +
' ' + UpperCase(FieldByName('STATE').AsString) + ' ' + FieldByName('ZIPCODE').AsString;
ProcessList[i, 3] := GetTotalHours(FieldByName('JURNUM').AsString);
ProcessList[i, 4] := ProcessList[i, 3] * payrate;
ProcessList[i, 5] := GetTotalMiles;
ProcessList[i, 6] := ProcessList[i, 5] * milerate;
ProcessList[i, 7] := FieldByName('JURNUM').AsString;
JMSData.qryHoursCalc.Next;
tripsmade := 0;
end;
firstvoucher := JMSData.daLastVoucher.Value;
//start excel
excelapp:= CreateOleObject('Excel.Application');
excelapp.Visible := True;
excelapp.Workbooks.Open('R:\Case Management\JMS\Forms\Vouchers.xls');
excelsht := excelapp.WorkSheets.Item['Data'];
excelsht.Activate;
//set excel range to size of ProcessList array and transfer array in full
excelsht.Range[excelsht.Cells.Item[2, 1], excelsht.Cells.Item[(personcount + 1), 8]].Value := ProcessList;
excelsht.Cells[1,10] := firstvoucher;
excelapp.Worksheets.Item['VoucherReport'].PageSetup.LeftFooter := 'Voucher Processing for term beginning: ' + frmMainMenu.ProcessDate;
excelapp.Run('Generate_VoucherRpt');
lastvoucher := excelsht.Cells[1, 10].Text;
//JMSData.daLastVoucher.value := lastvoucher;
PaidJurorList := TStringList.Create;
PaidJurorList.CommaText := excelapp.Worksheets.Item['Criteria'].Range['jurorlist'];
//create check records for paid jurors
end;
end;
finally
Screen.Cursor := Save_Cursor;
end;
end;
