Add blank column, rename column.
[capital-apms-progress.git] / process / report / outgoing.p
blobda55a3359fe8d56b30dcde0dce31414bd29ac4c6
1 &ANALYZE-SUSPEND _VERSION-NUMBER UIB_v8r12
2 &ANALYZE-RESUME
3 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _DEFINITIONS Procedure
4 /*--------------------------------------------------------------------------
5 File :
6 Purpose :
7 Author(s) :
8 Created :
9 Notes :
10 ------------------------------------------------------------------------*/
11 /* *************************** Definitions ************************** */
12 DEF INPUT PARAMETER report-options AS CHAR NO-UNDO.
13 DEF VAR p-1 AS INT NO-UNDO INITIAL 0.
14 DEF VAR p-n AS INT NO-UNDO INITIAL 99999.
15 DEF VAR list-name AS CHAR NO-UNDO INITIAL ?.
16 DEF VAR preview AS LOGI NO-UNDO INITIAL No.
17 DEF VAR show-values AS LOGI NO-UNDO INITIAL No.
18 DEF VAR show-tenex AS LOGI NO-UNDO INITIAL No.
19 DEF VAR path-name AS CHAR NO-UNDO INITIAL "".
20 path-name = SESSION:TEMP-DIRECTORY.
21 RUN parse-parameters.
23 DEF WORK-TABLE og-acct NO-UNDO
24 FIELD AccountCode LIKE ChartOfAccount.AccountCode
25 FIELD ShortName AS CHAR FORMAT "X(9)"
26 FIELD Recovered AS DECIMAL
27 FIELD Gross AS DECIMAL
28 FIELD Vacant AS DECIMAL.
30 {inc/ofc-this.i}
31 {inc/ofc-set.i "AcctGrp-Tenex" "tenex-account-groups"}
32 IF NOT AVAILABLE(OfficeSetting) THEN tenex-account-groups = "".
34 /* _UIB-CODE-BLOCK-END */
35 &ANALYZE-RESUME
38 &ANALYZE-SUSPEND _UIB-PREPROCESSOR-BLOCK
40 /* ******************** Preprocessor Definitions ******************** */
42 &Scoped-define PROCEDURE-TYPE Procedure
46 /* _UIB-PREPROCESSOR-BLOCK-END */
47 &ANALYZE-RESUME
50 /* ************************ Function Prototypes ********************** */
52 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD build-og-account Procedure
53 FUNCTION build-og-account RETURNS INTEGER
54 ( /* parameter-definitions */ ) FORWARD.
56 /* _UIB-CODE-BLOCK-END */
57 &ANALYZE-RESUME
59 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION-FORWARD get-prop-og Procedure
60 FUNCTION get-prop-og RETURNS DECIMAL
61 ( INPUT doit AS LOGICAL, INPUT pc AS INTEGER, INPUT ac AS DECIMAL ) FORWARD.
63 /* _UIB-CODE-BLOCK-END */
64 &ANALYZE-RESUME
67 /* *********************** Procedure Settings ************************ */
69 &ANALYZE-SUSPEND _PROCEDURE-SETTINGS
70 /* Settings for THIS-PROCEDURE
71 Type: Procedure
72 Allow:
73 Frames: 0
74 Add Fields to: Neither
75 Other Settings: CODE-ONLY COMPILE
77 &ANALYZE-RESUME _END-PROCEDURE-SETTINGS
79 /* ************************* Create Window ************************** */
81 &ANALYZE-SUSPEND _CREATE-WINDOW
82 /* DESIGN Window definition (used by the UIB)
83 CREATE WINDOW Procedure ASSIGN
84 HEIGHT = .67
85 WIDTH = 30.14.
86 /* END WINDOW DEFINITION */
88 &ANALYZE-RESUME
92 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _INCLUDED-LIB Procedure
93 /* ************************* Included-Libraries *********************** */
95 {inc/method/m-txtrep.i}
97 /* _UIB-CODE-BLOCK-END */
98 &ANALYZE-RESUME
102 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _CUSTOM _MAIN-BLOCK Procedure
105 /* *************************** Main Block *************************** */
108 IF list-name = ? THEN
109 RUN for-property-range.
110 ELSE
111 RUN for-company-list.
113 IF NOT Preview THEN
114 MESSAGE "Export complete!" VIEW-AS ALERT-BOX INFORMATION
115 TITLE "Completed".
117 /* _UIB-CODE-BLOCK-END */
118 &ANALYZE-RESUME
121 /* ********************** Internal Procedures *********************** */
123 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE for-company-list Procedure
124 PROCEDURE for-company-list :
125 /*------------------------------------------------------------------------------
126 Purpose:
127 ------------------------------------------------------------------------------*/
128 DEF VAR i AS INT NO-UNDO.
129 DEF VAR n AS INT NO-UNDO.
130 DEF VAR company-code AS INT NO-UNDO.
132 FIND ConsolidationList WHERE ConsolidationList.Name = list-name NO-LOCK.
134 n = NUM-ENTRIES( ConsolidationList.CompanyList ).
135 DO i = 1 TO n:
136 company-code = INT( ENTRY( i, ConsolidationList.CompanyList ) ).
137 FOR EACH Property WHERE Property.Active
138 AND Property.CompanyCode = company-code NO-LOCK:
139 RUN property-outgoings.
140 END.
141 END.
142 END PROCEDURE.
144 /* _UIB-CODE-BLOCK-END */
145 &ANALYZE-RESUME
148 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE for-property-range Procedure
149 PROCEDURE for-property-range :
150 /*------------------------------------------------------------------------------
151 Purpose:
152 ------------------------------------------------------------------------------*/
153 FOR EACH Property WHERE Property.Active
154 AND Property.PropertyCode >= p-1
155 AND Property.PropertyCode <= p-n NO-LOCK:
156 RUN property-outgoings.
157 END.
158 END PROCEDURE.
160 /* _UIB-CODE-BLOCK-END */
161 &ANALYZE-RESUME
164 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE parse-parameters Procedure
165 PROCEDURE parse-parameters :
166 /*------------------------------------------------------------------------------
167 Purpose: Decode the command-line parameters
168 ------------------------------------------------------------------------------*/
169 DEF VAR i AS INT NO-UNDO.
170 DEF VAR token AS CHAR NO-UNDO.
172 {inc/showopts.i "report-options"}
174 DO i = 1 TO NUM-ENTRIES( report-options, "~n" ):
175 token = ENTRY( i, report-options, "~n" ).
176 CASE( ENTRY( 1, token ) ):
177 WHEN "CompanyList" THEN list-name = ENTRY(2,token).
178 WHEN "PropertyRange" THEN ASSIGN
179 p-1 = INT(ENTRY(2,token))
180 p-n = INT(ENTRY(3,token)) .
182 WHEN "Preview" THEN preview = Yes.
183 WHEN "Values" THEN show-values = Yes.
184 WHEN "Percentages" THEN show-values = No.
185 WHEN "ShowTenex" THEN show-tenex = Yes.
186 WHEN "Directory" THEN path-name = TRIM( SUBSTRING(token, LENGTH(ENTRY(1,token)) + 2) ).
187 END CASE.
188 END.
190 END PROCEDURE.
192 /* _UIB-CODE-BLOCK-END */
193 &ANALYZE-RESUME
196 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE print-outgoings Procedure
197 PROCEDURE print-outgoings :
198 /*------------------------------------------------------------------------------
199 Purpose: Print outgoings information for the leases/spaces of the property
200 ------------------------------------------------------------------------------*/
201 DEF INPUT PARAMETER times-amt AS LOGICAL NO-UNDO.
203 DEF VAR out-line AS CHAR NO-UNDO.
204 DEF VAR no-accounts AS INTEGER NO-UNDO.
205 DEF VAR i AS INTEGER NO-UNDO.
206 DEF VAR percentage AS DECIMAL NO-UNDO.
207 DEF VAR lease-rate AS DECIMAL NO-UNDO.
208 DEF VAR acct-amnt AS DECIMAL NO-UNDO.
209 DEF VAR non-zero AS LOGICAL NO-UNDO.
210 DEF VAR tot-amnt AS DEC NO-UNDO.
211 DEF VAR no-columns AS INT NO-UNDO INITIAL 7.
212 IF times-amt THEN no-columns = 8.
214 DEF BUFFER PrimarySpace FOR RentalSpace.
216 no-accounts = build-og-account().
217 out-line = '"Code","Tenant","Area description"'.
218 FOR EACH og-acct BY og-acct.AccountCode:
219 out-line = out-line + "," + STRING( og-acct.AccountCode, "9999.99").
220 END.
221 PUT UNFORMATTED out-line + (IF times-amt THEN ',"Total"' ELSE "") SKIP(1).
223 /* Net Leases */
224 FOR EACH TenancyLease NO-LOCK OF Property WHERE TenancyLease.LeaseStatus <> "PAST"
225 AND NOT TenancyLease.GrossLease,
226 FIRST PrimarySpace OF TenancyLease /* WHERE PrimarySpace.RentalSpaceCode = TenancyLease.PrimarySpace */
227 BY PrimarySpace.Level BY PrimarySpace.LevelSequence :
228 FIND Tenant WHERE Tenant.TenantCode = TenancyLease.TenantCode NO-LOCK NO-ERROR.
229 non-zero = No.
230 out-line = STRING( Tenant.tenantCode )
231 + ',"' + Tenant.Name + '"'
232 + ',"' + TenancyLease.AreaDescription + '"'.
233 tot-amnt = 0.
234 lease-rate = TenancyLease.OutgoingsRate.
235 IF NOT( CAN-FIND( FIRST RentalSpace OF TenancyLease WHERE RentalSpace.AreaType <> "C")) THEN DO:
236 IF lease-rate = 100 THEN lease-rate = 0.
237 END.
238 FOR EACH og-acct BY og-acct.AccountCode:
239 FIND TenancyOutgoing NO-LOCK OF TenancyLease WHERE TenancyOutgoing.AccountCode = og-acct.AccountCode NO-ERROR.
240 percentage = (IF AVAILABLE(TenancyOutgoing) THEN TenancyOutgoing.Percentage ELSE lease-rate ).
241 IF percentage = ? THEN percentage = 0. ELSE
242 percentage = percentage * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
243 out-line = out-line + "," + STRING( percentage ).
244 tot-amnt = tot-amnt + percentage.
245 IF percentage <> 0 THEN non-zero = Yes.
246 END.
247 IF PrimarySpace.AreaType <> "C" OR non-zero THEN
248 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP.
249 END.
250 PUT UNFORMATTED ",," + FILL( ',"' + FILL("-", no-columns) + '"', no-accounts) SKIP.
252 out-line = ',"Total Recoverable",'.
253 tot-amnt = 0.
254 FOR EACH og-acct BY og-acct.AccountCode:
255 out-line = out-line + ',' + STRING( og-acct.Recovered * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode )) .
256 tot-amnt = tot-amnt + og-acct.Recovered * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
257 END.
258 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP(2).
261 /* Gross Leases */
262 FOR EACH TenancyLease NO-LOCK OF Property WHERE TenancyLease.LeaseStatus <> "PAST"
263 AND TenancyLease.GrossLease,
264 FIRST PrimarySpace OF TenancyLease /* WHERE PrimarySpace.RentalSpaceCode = TenancyLease.PrimarySpace */
265 BY PrimarySpace.Level BY PrimarySpace.LevelSequence :
266 FIND Tenant WHERE Tenant.TenantCode = TenancyLease.TenantCode NO-LOCK NO-ERROR.
267 non-zero = No.
268 out-line = STRING( Tenant.tenantCode )
269 + ',"' + Tenant.Name + '"'
270 + ',"' + TenancyLease.AreaDescription + '"'.
271 percentage = 0.
272 tot-amnt = 0.
273 FOR EACH RentalSpace OF TenancyLease:
274 percentage = percentage + RentalSpace.OutgoingsPercentage.
275 END.
276 IF percentage = 0 THEN NEXT.
278 FOR EACH og-acct BY og-acct.AccountCode:
279 acct-amnt = percentage * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
280 out-line = out-line + ',' + STRING( acct-amnt) .
281 tot-amnt = tot-amnt + acct-amnt.
282 og-acct.Gross = og-acct.Gross + percentage.
283 END.
284 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP.
285 END.
286 PUT UNFORMATTED ",," + FILL( ',"' + FILL("-", no-columns) + '"', no-accounts) SKIP.
288 out-line = ',"Total Gross Leases",'.
289 tot-amnt = 0.
290 FOR EACH og-acct BY og-acct.AccountCode:
291 out-line = out-line + ',' + STRING( og-acct.Gross * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode )) .
292 tot-amnt = tot-amnt + og-acct.Gross * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
293 END.
294 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP(2).
297 FOR EACH RentalSpace NO-LOCK OF Property WHERE RentalSpace.AreaStatus = "V"
298 AND RentalSpace.OutgoingsPercentage <> 0
299 BY Level BY LevelSequence:
300 out-line = '"Vacant","' + TRIM( STRING( RentalSpace.Level, "->>>9/")) + TRIM( STRING( RentalSpace.LevelSequence, ">>>9 "))
301 + '","'+ RentalSpace.Description + '"'.
302 tot-amnt = 0.
303 FOR EACH og-acct BY og-acct.AccountCode:
304 percentage = RentalSpace.OutgoingsPercentage * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
305 out-line = out-line + ',' + STRING( percentage ).
306 tot-amnt = tot-amnt + percentage.
307 og-acct.Vacant = og-acct.Vacant + RentalSpace.OutgoingsPercentage.
308 END.
309 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP.
310 END.
312 PUT UNFORMATTED ",," + FILL( ',"' + FILL("-", no-columns) + '"', no-accounts) SKIP.
314 out-line = ',"Total Vacant",'.
315 tot-amnt = 0.
316 FOR EACH og-acct BY og-acct.AccountCode:
317 out-line = out-line + ',' + STRING( og-acct.Vacant * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode )) .
318 tot-amnt = tot-amnt + og-acct.Vacant * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
319 END.
320 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP(2).
322 PUT UNFORMATTED ",," + FILL( ',"' + FILL("=", no-columns) + '"', no-accounts) SKIP.
323 out-line = ',"Total",'.
324 tot-amnt = 0.
325 FOR EACH og-acct BY og-acct.AccountCode:
326 out-line = out-line + ',' + STRING( (og-acct.Recovered + og-acct.Gross + og-acct.Vacant) * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode )) .
327 tot-amnt = tot-amnt + (og-acct.Recovered + og-acct.Gross + og-acct.Vacant) * get-prop-og( times-amt, Property.PropertyCode, og-acct.AccountCode ).
328 END.
329 PUT UNFORMATTED out-line + (IF times-amt THEN ',' + STRING(tot-amnt) ELSE "") SKIP.
331 END PROCEDURE.
333 /* _UIB-CODE-BLOCK-END */
334 &ANALYZE-RESUME
337 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _PROCEDURE property-outgoings Procedure
338 PROCEDURE property-outgoings :
339 /*------------------------------------------------------------------------------
340 Purpose:
341 ------------------------------------------------------------------------------*/
342 DEF VAR out-filename AS CHAR NO-UNDO.
344 FOR EACH og-acct: DELETE og-acct. END.
345 path-name = REPLACE( path-name, "\", "/" ).
346 out-filename = path-name
347 + (IF SUBSTRING( path-name, LENGTH(path-name)) <> "/" THEN "/" ELSE "")
348 + "P-" + STRING( Property.PropertyCode, "99999") + ".CSV".
349 OUTPUT TO VALUE( out-filename ) KEEP-MESSAGES.
350 RUN print-outgoings( show-values ).
351 OUTPUT CLOSE.
352 END PROCEDURE.
354 /* _UIB-CODE-BLOCK-END */
355 &ANALYZE-RESUME
358 /* ************************ Function Implementations ***************** */
360 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION build-og-account Procedure
361 FUNCTION build-og-account RETURNS INTEGER
362 ( /* parameter-definitions */ ) :
363 /*------------------------------------------------------------------------------
364 Purpose:
365 Notes:
366 ------------------------------------------------------------------------------*/
367 DEF VAR no-accounts AS INT NO-UNDO INITIAL 0.
369 FOR EACH TenancyLease NO-LOCK OF Property WHERE TenancyLease.LeaseStatus <> "PAST"
370 AND NOT TenancyLease.GrossLease,
371 EACH TenancyOutgoing NO-LOCK OF TenancyLease WHERE TenancyOutgoing.Percentage > 0:
372 FIND FIRST og-acct WHERE og-acct.AccountCode = TenancyOutgoing.AccountCode NO-ERROR.
373 IF AVAILABLE(og-acct) THEN
374 og-acct.Recovered = og-acct.Recovered + TenancyOutgoing.Percentage.
375 ELSE DO:
376 no-accounts = no-accounts + 1.
377 FIND ChartOfAccount WHERE ChartOfAccount.AccountCode = TenancyOutgoing.AccountCode NO-LOCK NO-ERROR.
378 CREATE og-acct.
379 ASSIGN
380 og-acct.AccountCode = TenancyOutgoing.AccountCode
381 og-acct.ShortName = (IF AVAILABLE(ChartOfAccount) THEN STRING( (IF TRIM(ChartOfAccount.ShortName) <> "" THEN ChartOfAccount.ShortName ELSE ChartOfAccount.Name), "X(7)") ELSE "???????")
382 og-acct.Recovered = TenancyOutgoing.Percentage
383 og-acct.Vacant = 0
385 og-acct.ShortName = TRIM(og-acct.ShortName).
386 og-acct.ShortName = FILL( " ", 7 - LENGTH(og-acct.ShortName)) + og-acct.ShortName .
387 END.
388 END.
390 /* fill in those where a default percentage applies from the lease record */
391 FOR EACH TenancyLease NO-LOCK OF Property WHERE TenancyLease.LeaseStatus <> "PAST"
392 AND NOT TenancyLease.GrossLease,
393 EACH og-acct WHERE NOT CAN-FIND( FIRST TenancyOutgoing OF TenancyLease WHERE TenancyOutgoing.AccountCode = og-acct.AccountCode):
394 IF CAN-FIND( FIRST RentalSpace OF TenancyLease WHERE RentalSpace.AreaType <> "C") OR TenancyLease.OutgoingsRate <> 100 THEN
395 og-acct.Recovered = og-acct.Recovered + TenancyLease.OutgoingsRate .
396 END.
398 RETURN no-accounts.
400 END FUNCTION.
402 /* _UIB-CODE-BLOCK-END */
403 &ANALYZE-RESUME
406 &ANALYZE-SUSPEND _UIB-CODE-BLOCK _FUNCTION get-prop-og Procedure
407 FUNCTION get-prop-og RETURNS DECIMAL
408 ( INPUT doit AS LOGICAL, INPUT pc AS INTEGER, INPUT ac AS DECIMAL ) :
409 /*------------------------------------------------------------------------------
410 Purpose:
411 Notes:
412 ------------------------------------------------------------------------------*/
413 IF doit <> Yes THEN RETURN 1.0 .
414 FIND PropertyOutgoing WHERE PropertyOutgoing.PropertyCode = pc
415 AND PropertyOutgoing.AccountCode = ac NO-LOCK NO-ERROR.
416 IF AVAILABLE(PropertyOutgoing) THEN
417 RETURN PropertyOutgoing.BudgetAmount / 100 .
419 RETURN 0.00 .
421 END FUNCTION.
423 /* _UIB-CODE-BLOCK-END */
424 &ANALYZE-RESUME