|
midrange.com
I hope this message isn't too long a reply.
This is a program that does a time billing analysis. It actually performs 5
SQL using 5 different cursors to grab data and print the report. It was
developed for in-house use (for me, by me) so I apologize for the sparcity
of comments.
Brian L.
*BEGINNING OF CODE
/*
*/
/* Copyright 1998 - H J M Systems, Inc.
*/
/* One Penn Plaza
*/
/* Suite 2812
*/
/* New York, NY 10119
*/
/*
*/
/* (212) 714-1055
*/
/*
*/
h/TITLE - Resource Performance -
h
ftbrspf ip e k disk
ftbcmpf if e k disk
fqsysprt o f 132 printer oflind(*inof)
d* Define Data Structure to determine budget days
d SQds1 DS
d SQBudget 3 0
d* Define Data Structure to retrieve employee time data
d SQds2 DS
d SQFirmChg 1
d SQHours 7 0
d SQBillFlg 1
d* Define Data Structure to find the first and
d* last day worked this year
d SQds3 DS
d HireDate 7 0
d TermDate 7 0
d/copy htools/source.cpy,$pgmstsile
d n$PgmTitle c 'Resource Performance Analysis'
d n$CmpName c '(c) 1998; HJM Systems, Inc.'
d* Define Work Fields
dStartDate s 7 0
dEndDate s 7 0
dHireYear s 4 0
dTermYear s 4 0
d#VacHours s 7 0
d#YVacation s 7 2
d* Define Printer Output fields
d* Occurrence 1 store MTD
d* Occurrence 2 store YTD
d#WorkFlds DS occurs(2)
d #Billable 6 2
d #NonBill 6 2
d #FirmChg 6 2
d #Vacation 6 2
d #NonChg 6 2
d #Budget 6 2
d #BudgetV# 6 2
d #BudgetVP 6 2
itbrsrcd 01
i rscoco l9
c *entry plist
c parm p$coco 3
c parm p$Year 4 0
c parm p$Month 2 0
c/space
c* First time logic
c if not *in99
c seton 99OF
c time #tod 12 0
c endif
c/space
c* Company break
c if *inl9
c rscoco chain tbcmrcd 40
c endif
c/space
c* Only report on Active and Employee types
c if rsacco = 'Y' and rsrtyp = '1'
c/space
c* Retrieve the earliest and latest work days for the employee
c/exec sql
c+ declare c3 cursor for
c+ SELECT min(t5dadt), max(t5dadt) FROM tbt5pf WHERE
c+ t5reco = :RSRECO and t5coco = :RSCOCO
c/end-exec
c/exec sql
c+ open c3
c/end-exec
c/exec sql
c+ fetch c3 into :SQds3
c/end-exec
c/exec sql
c+ close c3
c/end-exec
c* Calculate YTD
c 2 occur #WorkFlds
c clear #WorkFlds
c/space
c* Calculate Start Date for Employee
c eval StartDate =
c ((p$year - 1900) * 10000) + 101
c* Calculate End Date for Employee
c eval EndDate = ((p$year - 1900) * 10000)
c + (p$month * 100) + 31
c/space
c* Bypass Inactive Employees
c if StartDate > TermDate
c or EndDate < HireDate
c goto #End
c endif
c/space
c* If the Start Date is earlier than the date of Hire
c* then make it the date of hire
c if StartDate < HireDate
c eval StartDate = HireDate
c endif
c/space
c* If the End Date is later than the date of Termination
c* or the Last day worked then make it the date of Termination
c if EndDate > TermDate
c eval EndDate = TermDate
c endif
c/space
c* Extract Hire Month and Year
c eval HireYear = (HireDate / 10000) + 1900
c eval TermYear = (TermDate / 10000) + 1900
c HireDate div 100 HireMonth 2 0
c/space
c* Extract Start Month
c StartDate div 100 StartMonth 2 0
c/space
c* Extract End Month
c EndDate div 100 EndMonth 2 0
c/space
c* Calculate Years of employement
c TermYear sub HireYear #YearEmp 7 0
c* Of hired before July then also count the first year
c if HireMonth < 7
c add 1 #YearEmp
c endif
c/space
c* Calculate Personal Days
c eval #VacHours =
c (8 * ((EndMonth - StartMonth) + 1))
c if #VacHours > 40
c eval #VacHours = 40
c endif
c/space
c #VacHours div 8 #PerDays 2 0
c/space
c* Now Calculate Vacation Days
c select
c when #YearEmp = 1
c eval #VacHours = #VacHours + 40
c z-add 5 #VacDays
c when #YearEmp = 2
c eval #VacHours = #VacHours + 80
c z-add 10 #VacDays
c when #YearEmp > 2
c eval #VacHours = #VacHours + 120
c z-add 15 #VacDays
c other
c clear #VacDays 2 0
c endsl
c/space
c* Calculate the scheduled number of work days for the employee
c/exec sql
c+ declare c1 cursor for
c+ SELECT count(*) FROM tbcapf
c+ WHERE cadadt between :StartDate and :EndDate and
c+ cawoda = 'Y'
c/end-exec
c/exec sql
c+ open c1
c/end-exec
c/exec sql
c+ fetch c1 into :SQds1
c/end-exec
c/exec sql
c+ close c1
c/end-exec
c/space
c* Calculate the Total number of hours worked by project class
c/exec sql
c+ declare c2 cursor for
c+ SELECT ifnull(cpfich,' '), sum(T5HOUR), T5BIFL FROM tbt5pf left
c+ outer join tbcppf on t5coco = cpcoco and t5clco = cpclco and t5prco
c+ = cpprco WHERE t5dadt between :StartDate and :EndDate and
c+ t5reco = :RSRECO and t5coco = :RSCOCO GROUP
c+ BY cpfich,t5bifl
c/end-exec
c/exec sql
c+ open c2
c/end-exec
c/exec sql
c+ fetch c2 into :SQds2
c/end-exec
c dow sqlcod = 0
c select
c* Firm Chargeable
c when SQFirmChg = 'F'
c add SQHours #FirmChg
c* Non-Chargeable
c when SQFirmChg = 'N'
c add SQHours #NonChg
c* Vacation
c when SQFirmChg = 'V'
c add SQHours #Vacation
c* Holiday
c when SQFirmChg = 'H'
c* Non-Billable
c when SQBillFlg <> 'Y'
c add SQHours #NonBill
c* Billable
c other
c add SQHours #Billable
c endsl
c/space
c/exec sql
c+ fetch c2 into :SQds2
c/end-exec
c enddo
c/exec sql
c+ close c2
c/end-exec
c* Move a portion of vacation hours into firm chargeable
c* if within the employee limits
c sub #VacHours #Vacation
c add #VacHours #FirmChg
c if #Vacation < 0
c add #Vacation #FirmChg
c*** clear #Vacation
c clear #YVacation
c else
c eval #YVacation = #Vacation
c endif
c/space
c eval #Budget = SQBudget * 8
c eval #BudgetV# = (#Billable+#FirmChg)-#Budget
c if #Budget <> 0
c eval(h) #BudgetVP = (#BudgetV#/#Budget)*100
c else
c eval(h) #BudgetVP = 0
c endif
c/space
c* Calculate MTD
c 1 occur #WorkFlds
c clear #WorkFlds
c/space
c eval StartDate =
c ((p$year - 1900) * 10000)
c + (p$month * 100) + 01
c eval EndDate =
c ((p$year - 1900) * 10000)
c + (p$month * 100) + 31
c/space
c if StartDate < HireDate
c eval StartDate = HireDate
c endif
c/space
c if EndDate > TermDate
c eval EndDate = TermDate
c endif
c/space
c/exec sql
c+ declare c4 cursor for
c+ SELECT count(*) FROM tbcapf
c+ WHERE cadadt between :StartDate and :EndDate and
c+ cawoda = 'Y'
c/end-exec
c/exec sql
c+ open c4
c/end-exec
c/exec sql
c+ fetch c4 into :SQds1
c/end-exec
c/exec sql
c+ close c4
c/end-exec
c/space
c/exec sql
c+ declare c5 cursor for
c+ SELECT ifnull(cpfich,' '), sum(T5HOUR), T5BIFL FROM tbt5pf left
c+ outer join tbcppf on t5coco = cpcoco and t5clco = cpclco and t5prco
c+ = cpprco WHERE t5dadt between :StartDate and :EndDate and
c+ t5reco = :RSRECO and t5coco = :RSCOCO GROUP
c+ BY cpfich,t5bifl
c/end-exec
c/exec sql
c+ open c5
c/end-exec
c/exec sql
c+ fetch c5 into :SQds2
c/end-exec
c dow sqlcod = 0
c select
c* Firm Chargeable
c when SQFirmChg = 'F'
c add SQHours #FirmChg
c* Non-Chargeable
c when SQFirmChg = 'N'
c add SQHours #NonChg
c* Vacation
c when SQFirmChg = 'V'
c add SQHours #Vacation
c* Holiday
c when SQFirmChg = 'H'
c* Non-Billable
c when SQBillFlg <> 'Y'
c add SQHours #NonBill
c* Billable
c other
c add SQHours #Billable
c endsl
c/space
c/exec sql
c+ fetch c5 into :SQds2
c/end-exec
c enddo
c/exec sql
c+ close c5
c/end-exec
c if #Vacation > #YVacation
c add #Vacation #FirmChg
c sub #YVacation #FirmChg
c z-add #YVacation #Vacation
c endif
c/space
c eval #Budget = SQBudget * 8
c eval #BudgetV# = (#Billable+#FirmChg)-#Budget
c*** eval(h) #BudgetVP = (#BudgetV#/#Budget)*100
c if #Budget <> 0
c eval(h) #BudgetVP = (#BudgetV#/#Budget)*100
c else
c eval(h) #BudgetVP = 0
c endif
c/space
c 1 occur #WorkFlds
c except #DetailMTD
c 2 occur #WorkFlds
c except #DetailYTD
c endif
c/space
c #End tag
oqsysprt h of 1 4
o ##prog
o ##cdt +1
o n$PgmTitle 75
o #tod 122 '0 . . & / / '
o 128 'Page'
o page z
o h of 1
o n$CmpName 75
o h of 1
o cmcon0 75
o h of 2
o 65 'For Month of '
o p$Month
o '/'
o p$Year
o h of 1
o 60 'Firm '
o 72 'Non- '
o 84 'Excess Vac/'
o 96 'Non- '
o 120 'Budget '
o 132 'Budget '
o 61 '|'
o 97 '|'
o h of 1
o 48 'Billable'
o 60 'Chargeable'
o 72 'Billable'
o 84 'Personal '
o 96 'Chargeable'
o 108 'Budget '
o 120 'Variance'
o 132 'Variance'
o 61 '|'
o 97 '|'
o h of 1
o 'Resource'
o 48 'Hours '
o 60 'Hours '
o 72 'Hours '
o 84 'Hours '
o 96 'Hours '
o 108 'Hours '
o 120 'Hours '
o 132 'Percent'
o 61 '|'
o 97 '|'
o ef #DetailMTD 1
o rsreco +1
o rsname +1
o 38 'M-T-D'
o #Billable j 48
o #FirmChg j 60
o '|'
o #NonBill j 72
o #Vacation j 84
o #NonChg j 96
o '|'
o #Budget j 108
o #BudgetV# j 120
o #BudgetVP j 132
o/space
o ef #DetailYTD 1
o 'Allowed:'
o #VacDays j
o 'Vac.Days +'
o #PerDays j
o 'Per.Days'
o 38 'Y-T-D'
o #Billable j 48
o #FirmChg j 60
o '|'
o #NonBill j 72
o #Vacation a 85
o #NonChg j 96
o '|'
o #Budget j 108
o #BudgetV# j 120
o #BudgetVP j 132
o ef #DetailYTD 1
o 61 '|'
o 97 '|'
*END OF CODE
-----Original Message-----
From: Anthony Mallozzi <mallozzi@spectra.net>
To: rpg400-l@midrange.com <rpg400-l@midrange.com>
Date: Tuesday, December 01, 1998 9:40 PM
Subject: Example of using embedded SQL
>Hello to all!
> I would like to see an example of using embedded SQL
>in RPG. Sample code would be appreciated.
>Thanks,
>Tony Mallozzi
>
>* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
>* This is the RPG/400 Discussion Mailing List! To submit a new *
>* message, send your mail to "RPG400-L@midrange.com". To unsubscribe *
>* from this list send email to MAJORDOMO@midrange.com and specify *
>* 'unsubscribe RPG400-L' in the body of your message. Questions should *
>* be directed to the list owner / operator: david@midrange.com *
>* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
>
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the RPG/400 Discussion Mailing List! To submit a new *
* message, send your mail to "RPG400-L@midrange.com". To unsubscribe *
* from this list send email to MAJORDOMO@midrange.com and specify *
* 'unsubscribe RPG400-L' in the body of your message. Questions should *
* be directed to the list owner / operator: david@midrange.com *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
