569 lines
17 KiB
OpenEdge ABL
569 lines
17 KiB
OpenEdge ABL
Option Explicit
|
|
Dim pres_right As Integer
|
|
|
|
Dim first_row As Integer
|
|
Dim current_row As Integer
|
|
Dim last_check_row As Integer
|
|
|
|
Dim amount_in_func As String
|
|
Dim starting_cell As String
|
|
|
|
Dim grand_total_curr as currency
|
|
Dim grand_total_str as string
|
|
|
|
Dim amount_in_func_curr as currency
|
|
Dim amount_in_func_str as string
|
|
|
|
Dim pcco_str as string
|
|
|
|
Dim PCCO_COL as string
|
|
Dim ACCOUNT_COL as string
|
|
|
|
Dim AMOUNT_IN_FUNC_COL as string
|
|
Dim GRAND_TOTAL_COL As String
|
|
Dim DEBIT_COL as string
|
|
Dim CREDIT_COL as string
|
|
Dim REMARKS_COL as string
|
|
|
|
Dim ADJUSTMENT_WORKSHEET as string
|
|
Dim ADJUSTMENT_TEST_WORKSHEET as string
|
|
|
|
Dim AC_1020 as string
|
|
Dim AC_8600 as string
|
|
Dim AC_8601 as string
|
|
Dim AC_8700 as string
|
|
Dim AC_8701 as string
|
|
Dim AC_1311 as string
|
|
Dim AC_1375 as string
|
|
Dim AC_2020 as string
|
|
|
|
Dim current_row_value_valid as boolean
|
|
Dim check_next_row_ready as boolean
|
|
|
|
function writeRemarks(current_row as integer, remarks as string)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(REMARKS_COL & CStr(current_row)).Value = remarks
|
|
end function
|
|
|
|
function writeCredit(current_row as integer, credit as variant)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(CREDIT_COL & CStr(current_row)).Value = abs(credit)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(CREDIT_COL & CStr(current_row)).NumberFormat = "#,##0.00"
|
|
|
|
end function
|
|
|
|
function writeDebit(current_row as integer, debit as variant)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(DEBIT_COL & CStr(current_row)).Value = abs(debit)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(DEBIT_COL & CStr(current_row)).NumberFormat = "#,##0.00"
|
|
end function
|
|
|
|
function writeAccount(current_row as integer, account as string)
|
|
Worksheets(ADJUSTMENT_WORKSHEET).Range(ACCOUNT_COL & CStr(current_row)).Value = account
|
|
end function
|
|
|
|
function getPCCO(current_row as integer)
|
|
Dim output as string
|
|
output = Worksheets(ADJUSTMENT_WORKSHEET).Range(PCCO_COL & CStr(current_row)).Value
|
|
getPCCO = output
|
|
end function
|
|
|
|
|
|
function getGrandTotal(current_row as integer)
|
|
Dim output as string
|
|
output = Worksheets(ADJUSTMENT_WORKSHEET).Range(GRAND_TOTAL_COL & CStr(current_row)).text
|
|
getGrandTotal = output
|
|
end function
|
|
|
|
function getAmountInFunc(current_row as integer)
|
|
Dim output as string
|
|
output = Worksheets(ADJUSTMENT_WORKSHEET).Range(AMOUNT_IN_FUNC_COL & CStr(current_row)).Value
|
|
debug.print output
|
|
getAmountInFunc = output
|
|
end function
|
|
|
|
function checkOutputCellReady(current_row as integer)
|
|
Dim output as boolean
|
|
output = True
|
|
|
|
Dim account_cell as variant
|
|
account_cell = Worksheets(ADJUSTMENT_WORKSHEET).Range(ACCOUNT_COL & CStr(current_row))
|
|
|
|
if (IsEmpty(account_cell) = False) then
|
|
output = False
|
|
end if
|
|
|
|
checkOutputCellReady = output
|
|
end function
|
|
|
|
|
|
function checkCurrentRowValueValid(current_row as integer)
|
|
Dim output as boolean
|
|
output = True
|
|
|
|
if (checkIfGrandTotalRefNotFound(current_row) = True) then
|
|
debug.print "grand total ref not found"
|
|
|
|
output = False
|
|
end if
|
|
|
|
Dim grand_total_str as string
|
|
dim amount_in_func_str as string
|
|
dim pcco_str as string
|
|
|
|
grand_total_str = getGrandTotal(current_row)
|
|
amount_in_func_str = getAmountInFunc(current_row)
|
|
pcco_str = getPCCO(current_row)
|
|
|
|
' Debug.print InStr(pcco_str, "Error") >= 0
|
|
' Debug.print InStr(amount_in_func_str, "N/A") >= 0
|
|
' Debug.print InStr(grand_total_str, "N/A") >= 0
|
|
|
|
if (trim(pcco_str) = "" or InStr(pcco_str, "N/A") > 0 or InStr(pcco_str, "Error") > 0) then
|
|
output = False
|
|
end if
|
|
|
|
if (trim(amount_in_func_str) = "" or InStr(amount_in_func_str, "N/A") > 0 or InStr(amount_in_func_str, "Error") > 0) then
|
|
output = False
|
|
end if
|
|
|
|
if (trim(grand_total_str) = "" or InStr(grand_total_str, "N/A") > 0 or InStr(grand_total_str, "Error") > 0) then
|
|
output = False
|
|
end if
|
|
|
|
checkCurrentRowValueValid = output
|
|
end function
|
|
|
|
function checkFullFillCasePCCOCode_P1375000Situation_1(grand_total as currency, amount_in_func as currency)
|
|
' ### Special case (if PCCO Code = P1375000):
|
|
' ### Situation 1:
|
|
' if Grand total + amount in func > 0 (missing example)
|
|
|
|
checkFullFillCasePCCOCode_P1375000Situation_1 = (grand_total + amount_in_func > 0)
|
|
end function
|
|
|
|
function checkFullFillCasePCCOCode_P1375000Situation_2(grand_total as currency, amount_in_func as currency)
|
|
' ### Special case (if PCCO Code = P1375000):
|
|
' ### Situation 2:
|
|
' if Grand total + amount in func < 0 (row 21)
|
|
|
|
checkFullFillCasePCCOCode_P1375000Situation_2 = (grand_total + amount_in_func < 0)
|
|
end function
|
|
|
|
function checkFullFillCasePCCOCode_P1375000Situation_3(grand_total as currency, amount_in_func as currency)
|
|
' ### Special case (if PCCO Code = P1375000):
|
|
' ### Situation 3:
|
|
' if Grand total + amount in func = 0
|
|
' - do nothing
|
|
|
|
checkFullFillCasePCCOCode_P1375000Situation_3 = (grand_total + amount_in_func = 0)
|
|
end function
|
|
|
|
function checkFullFillCaseCommonSituation_1(grand_total as currency, amount_in_func as currency)
|
|
' ### Situation 1:
|
|
' if Grand total + amount in func > 0 (row 8 ?)
|
|
checkFullFillCaseCommonSituation_1 = (grand_total + amount_in_func > 0)
|
|
end function
|
|
|
|
function checkFullFillCaseCommonSituation_2(grand_total as currency, amount_in_func as currency)
|
|
' ### Situation 2:
|
|
' if Grand total + amount in func < 0 (row 8 ?)'
|
|
checkFullFillCaseCommonSituation_2 = (grand_total + amount_in_func < 0)
|
|
end function
|
|
|
|
function checkFullFillCaseCommonSituation_3(grand_total as currency, amount_in_func as currency)
|
|
' ### Situation 3:
|
|
' if Grand total + amount in func = 0
|
|
checkFullFillCaseCommonSituation_3 = (grand_total + amount_in_func = 0)
|
|
end function
|
|
|
|
function checkIfReferenceNotFound(cell as variant)
|
|
Dim test_text as variant
|
|
test_text = cell.text
|
|
checkIfReferenceNotFound = (InStr(test_text, "#REF!") > 0)
|
|
end function
|
|
|
|
function checkIfGrandTotalRefNotFound(current_row as integer)
|
|
checkIfGrandTotalRefNotFound = checkIfReferenceNotFound (Worksheets(ADJUSTMENT_WORKSHEET).Range(GRAND_TOTAL_COL & CStr(current_row)))
|
|
end function
|
|
|
|
function applyCommonSituation_1(current_row as integer)
|
|
Dim net_var as variant
|
|
Dim grand_total_var as variant
|
|
Dim amount_in_func_var as variant
|
|
|
|
grand_total_var = CDec(getGrandTotal(current_row))
|
|
amount_in_func_var = CDec(getAmountInFunc(current_row))
|
|
net_var = grand_total_var + amount_in_func_var
|
|
|
|
' current row
|
|
' - Account column: A1311, Debit column: Grand Total+amount in func
|
|
writeAccount current_row, AC_1311
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:1020,
|
|
' - debit column: Grand Total+amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_1020
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:NA8600,
|
|
' - debit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8600
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account NA8601,
|
|
' - credit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8601
|
|
writeCredit current_row, net_var
|
|
|
|
applyCommonSituation_1 = True
|
|
end function
|
|
|
|
function applyCommonSituation_2(current_row as integer)
|
|
Dim net_var as variant
|
|
Dim grand_total_var as variant
|
|
Dim amount_in_func_var as variant
|
|
|
|
grand_total_var = CDec(getGrandTotal(current_row))
|
|
amount_in_func_var = CDec(getAmountInFunc(current_row))
|
|
net_var = grand_total_var + amount_in_func_var
|
|
|
|
' if Grand total + amount in func < 0 (row 8 ?)
|
|
' - Account column: A1311,
|
|
' - Credit column: Grand Total+amount in func
|
|
writeAccount current_row, AC_1311
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:1020,
|
|
' - credit column: Grand Total+amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_1020
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:NA8700,
|
|
' - credit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8700
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account NA8701,
|
|
' - debit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8701
|
|
writeDebit current_row, net_var
|
|
|
|
applyCommonSituation_2 = True
|
|
end function
|
|
|
|
function applyCommonSituation_3(current_row as integer)
|
|
' if Grand total + amount in func = 0
|
|
' - do nothing
|
|
applyCommonSituation_3 = True
|
|
end function
|
|
|
|
function applyPcco1375Situation_1(current_row as integer)
|
|
Dim net_var as variant
|
|
Dim grand_total_var as variant
|
|
Dim amount_in_func_var as variant
|
|
|
|
grand_total_var = CDec(getGrandTotal(current_row))
|
|
amount_in_func_var = CDec(getAmountInFunc(current_row))
|
|
net_var = grand_total_var + amount_in_func_var
|
|
|
|
' - Account
|
|
' - column: P1375,
|
|
' - Debit column: Grand Total+amount in func
|
|
writeAccount current_row, AC_1375
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:2020,
|
|
' - debit column: Grand Total+amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_2020
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:NA8600,
|
|
' - debit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8600
|
|
writeDebit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account NA8601,
|
|
' - credit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8601
|
|
writeCredit current_row, net_var
|
|
|
|
applyPcco1375Situation_1 = True
|
|
end function
|
|
|
|
function applyPcco1375Situation_2(current_row as integer)
|
|
Dim net_var as variant
|
|
Dim grand_total_var as variant
|
|
Dim amount_in_func_var as variant
|
|
|
|
grand_total_var = CDec(getGrandTotal(current_row))
|
|
amount_in_func_var = CDec(getAmountInFunc(current_row))
|
|
net_var = grand_total_var + amount_in_func_var
|
|
|
|
' - Account
|
|
' - column: P1375,
|
|
' - Credit column: Grand Total+amount in func
|
|
writeAccount current_row, AC_1375
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:2020,
|
|
' - credit column: Grand Total+amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_2020
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account:8700,
|
|
' - credit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8700
|
|
writeCredit current_row, net_var
|
|
|
|
' - add row:
|
|
' - account 8701,
|
|
' - debit: grand total + amount in func
|
|
insertNewRowBelow current_row
|
|
current_row = current_row+ 1
|
|
writeAccount current_row, AC_8701
|
|
writeDebit current_row, net_var
|
|
|
|
applyPcco1375Situation_2= True
|
|
end function
|
|
|
|
function applyPcco1375Situation_3(current_row as integer)
|
|
' - do nothing
|
|
applyPcco1375Situation_3 = True
|
|
end function
|
|
|
|
|
|
Sub test()
|
|
' Debug.Assert True = isCurrentRowNeedToProcess("7")
|
|
' Debug.Assert False = isCurrentRowNeedToProcess("180")
|
|
' debug.assert False = isAllDone ("7")
|
|
' debug.assert True = isAllDone ("180")
|
|
|
|
' debug.assert False = checkFullFillCaseCommonSituation_1(-10,1)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_1(1,-10)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_1(-10,-10)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_1(0,0)
|
|
' debug.assert True = checkFullFillCaseCommonSituation_1(0,1)
|
|
' debug.assert True = checkFullFillCaseCommonSituation_1(1,0)
|
|
' debug.assert True = checkFullFillCaseCommonSituation_1(1,1)
|
|
|
|
' debug.assert True = checkFullFillCaseCommonSituation_2(-10,1)
|
|
' debug.assert True = checkFullFillCaseCommonSituation_2(1,-10)
|
|
' debug.assert True = checkFullFillCaseCommonSituation_2(-10,-10)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_2(0,0)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_2(0,1)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_2(1,0)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_2(1,1)
|
|
|
|
' debug.assert True = checkFullFillCaseCommonSituation_3(0,0)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_3(1,1)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_3(-10,-10)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_3(1,0)
|
|
' debug.assert False = checkFullFillCaseCommonSituation_3(0,1)
|
|
|
|
' debug.print Worksheets("Adjustment_TEST").Range("G86")
|
|
|
|
init
|
|
|
|
current_row = 29
|
|
' amount_in_func_str = getAmountInFunc(current_row)
|
|
' pcco_str = getPCCO(current_row)
|
|
|
|
' debug.print current_row
|
|
' debug.print grand_total_str
|
|
' debug.print checkIfGrandTotalRefNotFound(current_row)
|
|
debug.print checkCurrentRowValueValid(current_row)
|
|
|
|
' current_row_value_valid = False
|
|
' current_row_value_valid = checkCurrentRowValueValid(pcco_str, amount_in_func_str, grand_total_str)
|
|
' check_next_row_ready = False
|
|
' check_next_row_ready = checkOutputCellReady(current_row)
|
|
|
|
End Sub
|
|
|
|
Function getLastCheckRow(first_row As Integer)
|
|
getLastCheckRow = first_row + 10
|
|
End Function
|
|
|
|
function insertNewRowBelow(current_row as integer)
|
|
Range("A"¤t_row+1).EntireRow.Insert
|
|
end function
|
|
|
|
function isAllDone(input_row as integer)
|
|
Dim cell_value As Currency
|
|
Dim output as Boolean
|
|
Dim i as integer
|
|
output = true
|
|
|
|
last_check_row = getLastCheckRow(input_row)
|
|
For i = input_row To last_check_row
|
|
if (isCurrentRowNeedToProcess(i) = True) then
|
|
output = false
|
|
Exit For
|
|
end if
|
|
Next i
|
|
|
|
isAllDone = output
|
|
end function
|
|
|
|
Function isCurrentRowNeedToProcess(input_row as integer)
|
|
Dim output As Boolean
|
|
Dim cell_value As Currency
|
|
Dim input_cell as string
|
|
input_cell = GRAND_TOTAL_COL & input_row
|
|
|
|
isCurrentRowNeedToProcess = not(IsEmpty(Worksheets(ADJUSTMENT_WORKSHEET).Range(input_cell).Value))
|
|
End Function
|
|
|
|
Sub init()
|
|
pres_right = 111
|
|
first_row = 7
|
|
current_row = first_row
|
|
last_check_row = getLastCheckRow(first_row)
|
|
|
|
|
|
GRAND_TOTAL_COL = "G"
|
|
amount_in_func = "J"
|
|
starting_cell = GRAND_TOTAL_COL & first_row
|
|
PCCO_COL = "I"
|
|
AMOUNT_IN_FUNC_COL = "J"
|
|
DEBIT_COL = "N"
|
|
CREDIT_COL= "O"
|
|
ACCOUNT_COL = "M"
|
|
REMARKS_COL = "Q"
|
|
|
|
AC_1020 = "AC_1020"
|
|
AC_8600 = "AC_8600"
|
|
AC_8601 = "AC_8601"
|
|
AC_8700 = "AC_8700"
|
|
AC_8701 = "AC_8701"
|
|
AC_1311 = "AC_1311"
|
|
AC_1375 = "AC_1375"
|
|
AC_2020 = "AC_2020"
|
|
|
|
ADJUSTMENT_WORKSHEET = "Adjustment_TEST"
|
|
' ADJUSTMENT_WORKSHEET = "Adjustment"
|
|
|
|
end sub
|
|
|
|
Sub Main()
|
|
init
|
|
|
|
While isAllDone(current_row) = False
|
|
debug.print "processing row: " & current_row
|
|
debug.print checkCurrentRowValueValid(current_row)
|
|
|
|
|
|
|
|
current_row_value_valid = False
|
|
current_row_value_valid = checkCurrentRowValueValid(current_row)
|
|
check_next_row_ready = False
|
|
check_next_row_ready = checkOutputCellReady(current_row)
|
|
|
|
|
|
|
|
if (current_row_value_valid and check_next_row_ready) then
|
|
' input considered valid after this line
|
|
|
|
Dim grand_total_str as string
|
|
dim amount_in_func_str as string
|
|
dim pcco_str as string
|
|
|
|
grand_total_str = getGrandTotal(current_row)
|
|
amount_in_func_str = getAmountInFunc(current_row)
|
|
pcco_str = getPCCO(current_row)
|
|
|
|
debug.print "before"
|
|
debug.print grand_total_str
|
|
debug.print "pass"
|
|
grand_total_curr = ccur(grand_total_str)
|
|
|
|
amount_in_func_curr = ccur(amount_in_func_str)
|
|
|
|
if (pcco_str = "P1375000") then
|
|
if (checkFullFillCasePCCOCode_P1375000Situation_1(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "P1375000 common sit 1"
|
|
applyPcco1375Situation_1(current_row)
|
|
Elseif (checkFullFillCasePCCOCode_P1375000Situation_2(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "P1375000 common sit 2"
|
|
applyPcco1375Situation_2(current_row)
|
|
Elseif (checkFullFillCasePCCOCode_P1375000Situation_3(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "P1375000 common sit 3"
|
|
applyPcco1375Situation_3(current_row)
|
|
Else
|
|
msgbox "missing pcco 1375 situation"
|
|
end if
|
|
|
|
Elseif (pcco_str = "N/A") then
|
|
msgbox "pcco value is N/A"
|
|
|
|
Else
|
|
if (checkFullFillCaseCommonSituation_1(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "common sit 1"
|
|
applyCommonSituation_1(current_row)
|
|
Elseif (checkFullFillCaseCommonSituation_2(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "common sit 2"
|
|
applyCommonSituation_2(current_row)
|
|
Elseif (checkFullFillCaseCommonSituation_3(grand_total_curr, amount_in_func_curr) = True) then
|
|
writeRemarks current_row, "common sit 3"
|
|
applyCommonSituation_3(current_row)
|
|
Else
|
|
msgbox "missing situation"
|
|
end if
|
|
end if
|
|
|
|
else
|
|
debug.print "input is not valid, skipping row"
|
|
|
|
if (checkOutputCellReady(current_row)) then
|
|
if (checkCurrentRowValueValid(current_row)) then
|
|
else
|
|
writeRemarks current_row, "input is not valid"
|
|
end if
|
|
end if
|
|
end if
|
|
|
|
current_row = current_row + 1
|
|
Wend
|
|
|
|
End Sub
|
|
|
|
Sub helloworld()
|
|
' debug.print isCurrentRowNeedToProcess ("7")
|
|
' debug.print isCurrentRowNeedToProcess ("180")
|
|
|
|
' debug.print isAllDone ("7")
|
|
' debug.print isAllDone ("180")
|
|
|
|
End Sub
|