/**** example code****/
/*------------------------------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
------------------------------------------------------------------------------*/
DEFINE VARIABLE chExcel AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ROW_NO AS INTEGER NO-UNDO INIT 3.
DEFINE VARIABLE COL_ROW_NO AS INTEGER NO-UNDO INIT 28.
DEFINE VARIABLE DIF_ROW_NO AS INTEGER NO-UNDO INIT 53.
DEFINE VARIABLE LAST_MONTH_ARREARS AS DECIMAL NO-UNDO.
CREATE "excel.application" chExcel.
chExcel:Workbooks:Open("D:\Opensoft\GL\DOC\COLLECTION SUMMARY BUCKET WISE.xlsx").
RUN getMonth(INPUT MONTH(mSYSDATE), OUTPUT mMonth).
/*ARREARS REPORT HEADING*/
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):HorizontalAlignment = -4108.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):VerticalAlignment = -4108.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):Merge.
chExcel:Range(chExcel:Cells(1,1),chExcel:Cells(1,5)):VALUE = "BRANCH WISE COLLECTION ANALYSYS REPORT AS AT :- " + STRING(mSYSDATE,"99/99/9999").
/* MONTH END BALANCES */
ASSIGN grp1Arr = 0 grp2Arr = 0 grp3Arr = 0 grp4Arr = 0 grp5Arr = 0 TOT-Current = 0 grp6Arr = 0 grp7Arr = 0 grp8Arr = 0 grp11Arr = 0.
FOR EACH Arrears WHERE NO-LOCK BREAK BY tt-BRCode:
IF Arrears.tt-group-tag = "RLS" THEN /*SHORT FALLS CAN-DO("CA009894,CC009870,CK009882,CM009890,ID140817,IH004130,IH196077",Arrears.tt-FollowUpOfficer)*/
DO:
ASSIGN grp7Arr = grp7Arr + Arrears.tt-collection.
END.
ELSE IF Arrears.tt-group-tag = "LGL" THEN /*LEGAL ACTION*/
DO:
ASSIGN grp8Arr = grp8Arr + Arrears.tt-collection.
END.
ELSE IF Arrears.tt-group-tag = "REP" THEN /*REPOSSES Arrears.tt-FollowUpOfficer = "IY065500"*/
DO:
ASSIGN grp6Arr = grp6Arr + Arrears.tt-collection.
END.
ELSE
DO:
IF (tt-NoOfRentalsInArrears >= 0 AND tt-NoOfRentalsInArrears < 1) THEN
ASSIGN grp1Arr = grp1Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 1 AND tt-NoOfRentalsInArrears < 1.5) THEN
ASSIGN grp11Arr = grp11Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 1.5 AND tt-NoOfRentalsInArrears < 2) THEN
ASSIGN grp2Arr = grp2Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 2 AND tt-NoOfRentalsInArrears < 3) THEN
ASSIGN grp3Arr = grp3Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 3 AND tt-NoOfRentalsInArrears < 6) THEN
ASSIGN grp4Arr = grp4Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 6 AND tt-NoOfRentalsInArrears < 9) THEN
ASSIGN grp5Arr = grp5Arr + Arrears.tt-collection.
ELSE IF (tt-NoOfRentalsInArrears >= 9) THEN
ASSIGN grp9Arr = grp9Arr + Arrears.tt-collection.
END.
IF LAST-OF(tt-BRCode) THEN
DO:
FIND FIRST branches WHERE branches.brncode = tt-BRCode NO-LOCK NO-ERROR.
IF AVAILABLE(branches) THEN
DO:
ASSIGN chExcel:Cells(ROW_NO,1):VALUE = corpdata.branches.descr. /*ARREARS TABLE*/
END.
ELSE
DO:
ASSIGN chExcel:Cells(ROW_NO,1):VALUE = "BRANCH NOT FOUND". /*ARREARS TABLE*/
END.
ASSIGN chExcel:Cells(ROW_NO,2):VALUE = grp1Arr
chExcel:Cells(ROW_NO,3):VALUE = grp11Arr
chExcel:Cells(ROW_NO,4):VALUE = grp2Arr
chExcel:Cells(ROW_NO,5):VALUE = grp3Arr
chExcel:Cells(ROW_NO,6):VALUE = grp4Arr
chExcel:Cells(ROW_NO,7):VALUE = grp5Arr
chExcel:Cells(ROW_NO,8):VALUE = grp9Arr
chExcel:Cells(ROW_NO,10):VALUE = grp6Arr
chExcel:Cells(ROW_NO,11):VALUE = grp7Arr
chExcel:Cells(ROW_NO,12):VALUE = grp8Arr.
ASSIGN ROW_NO = ROW_NO + 1.
ASSIGN grp1Arr = 0 grp2Arr = 0 grp3Arr = 0 grp4Arr = 0 grp5Arr = 0 TOT-Current = 0 grp6Arr = 0 grp7Arr = 0 grp8Arr = 0 grp9Arr = 0 grp11Arr = 0.
END.
END.
/*END OF MONTH END BALANCES */
chExcel:visible = false.
IF VALID-HANDLE(vchWorkbook) THEN DO:
vchWorkbook:SaveAs("E:\DailyReports\RecoveryDashboard.xlsx",51,,,,,) NO-ERROR.
IF ERROR-STATUS:GET-MESSAGE(1) MATCHES "*SaveAs*" THEN
MESSAGE "There was an error trying to save the workbook." VIEW-AS ALERT-BOX ERROR.
END.
RELEASE OBJECT chWorksheet NO-ERROR.
RELEASE OBJECT chWorkbook NO-ERROR.
RELEASE OBJECT chExcel NO-ERROR.
END PROCEDURE.
/*** end of code***/