Lua: Followup transactions

Page 1 of 1
7 posts
Lua: Followup transactions

Nikolay
MMEX Developer

Posts: 2303
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
    html = {}
    function html:init ()
       o = {}
       setmetatable(o, self)
       self.__index = self
       self.html = mmHTMLBuilder("Init")
       self.error_description = ''
       return o
    end
    function html:start_table (v)
       self.html = self.html .. mmHTMLBuilder("StartTable", v)
       self.colour1 = "#E1EDFB"
       self.rowid = 0
       return o
    end
    function html:end_table ()
       self.html = self.html .. mmHTMLBuilder("EndTable")
    end
    function html:add_header (v)
       self.html = self.html .. mmHTMLBuilder("StartTableRow")
          for i in pairs(v) do
             if type(i) == "number" then
                self.html = self.html .. mmHTMLBuilder("AddTableHeaderCell", v[i])
             end
          end
       self.html = self.html .. mmHTMLBuilder("EndTableRow")
    end
    function html:start_row (v)
       local colour = ""
       if self.rowid%2 == 1 then colour = self.colour1 end
       self.html = self.html .. mmHTMLBuilder("StartTableRow", colour)
       self.rowid = self.rowid +1
    end
    function html:end_row ()
       self.html = self.html .. mmHTMLBuilder("EndTableRow")
    end
    function html:add_cell (v, n)
       self.html = self.html .. mmHTMLBuilder("AddTableCell", v, n)
    end
    function html:add_link (v, h)
       self.html = self.html .. mmHTMLBuilder("AddTableCellLink", v, h)
    end
    function html:push_error(v)
       self.error_description = self.error_description .. '<br>' .. v
    end
    function html:output_errors()
       self.html = self.html .. '<br><br>' .. self.error_description
    end
    function html:get_transactions()
       local sql = [[
         select c.transid, c.transdate
         ,a.accountname
         , case c.transcode when 'Transfer' then t.accountname else p.payeename end payee
         , ca.categname || case  c.subcategid when -1 then '' else ':'||S.SUBCategName end
         , case c.transcode when 'Deposit' then  c.transamount else -c.transamount end
         , c.notes
         , cur.currency_symbol
         from checkingaccount_v1 c
         left join payee_v1 p on p.payeeid = c.payeeid
         left join category_v1 ca on c.categid = ca.categid
         left join SUBCATEGORY_V1 S on S.SUBCATEGID = C.SUBCATEGID and C.CATEGID = S.CATEGID
         left join ACCOUNTLIST_V1 a on a.ACCOUNTID = C.ACCOUNTID
         left join ACCOUNTLIST_V1 t on t.ACCOUNTID = C.toACCOUNTID
         left join CURRENCYFORMATS_V1 cur on cur.currencyid = a.currencyid
         where c.status = 'F'
         order by c.transdate
       ]]
       v, err = mmGetSQLResultSet(sql)
       if err ~= 0
          then self:push_error(v)
          x = 0
       else
          x = SQLResultSetX
          self:add_header({_("Account"), _("Date"), _("Payee"), _("Category"), _("Amount"), _("Notes")})
       end
       for i = 1, x, 1 do
          self:start_row()
          self:add_link("TRXID:" .. SQLResultSet[i][1], SQLResultSet[i][3])
          self:add_cell(mmDateFormat(SQLResultSet[i][2]))
          self:add_cell(SQLResultSet[i][4])
          self:add_cell(SQLResultSet[i][5])
          self:add_cell(mmCurrencyFormat(SQLResultSet[i][8], SQLResultSet[i][6]), 1)
          self:add_cell(SQLResultSet[i][7])
          self:end_row()
       end
    end

    -----------------------------------------------------------
    --Main function
    -----------------------------------------------------------
    html:init()
    html:start_table("100%")
    html:get_transactions()
    html:end_table()
    html:output_errors()

    return html.html

Re: Lua: Followup transactions

Nikolay
MMEX Developer

Posts: 2303
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
The script updated.
Re: Lua: Followup transactions

avma
Super MMEX User

Posts: 43
Joined: Wed Sep 02, 2009 12:47 am
Location: Israel
hi,

this report stopped working on svn 4428, i'm now getting this message:
Error: [string " html = {}..."]:11: attempt to concatenate field 'html'

thanks
avi
Re: Lua: Followup transactions

siena123
MMEX Developer

Posts: 13
Joined: Wed Jan 23, 2013 7:00 pm
There was a change to the mmHTMLBuilder syntax in svn 3997. It no longer returns a value so the code listed gives the error mentioned. The following code works for svn 3997 and later.
Followup_Transactions.lua
(2.95 KiB) Downloaded 881 times
Re: Lua: Followup transactions

Nikolay
MMEX Developer

Posts: 2303
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
That report use raw sql. That is bad.
New ideology provided in the script: http://sourceforge.net/p/moneymanagerex ... _stats.lua
Lua script should have main function to be possible to call from c++ side if needed.

Insteed raw sql it should be developed c++ side function like get_followup_transactions()
Re: Lua: Followup transactions

avma
Super MMEX User

Posts: 43
Joined: Wed Sep 02, 2009 12:47 am
Location: Israel
siena123 wrote:There was a change to the mmHTMLBuilder syntax in svn 3997. It no longer returns a value so the code listed gives the error mentioned. The following code works for svn 3997 and later.
Followup_Transactions.lua
great, thanks


Great!

Thanks
Re: Lua: Followup transactions

Nikolay
MMEX Developer

Posts: 2303
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
        select c.transid, c.transdate
         ,a.accountname
         , case c.transcode when 'Transfer' then t.accountname else p.payeename end payee
         , ca.categname || case  c.subcategid when -1 then '' else ':'||S.SUBCategName end as Category
         , case c.transcode when 'Deposit' then  c.transamount else -c.transamount end as Amount
         , c.notes
         , cur.currency_symbol
         from checkingaccount_v1 c
         left join payee_v1 p on p.payeeid = c.payeeid
         left join category_v1 ca on c.categid = ca.categid
         left join SUBCATEGORY_V1 S on S.SUBCATEGID = C.SUBCATEGID and C.CATEGID = S.CATEGID
         left join ACCOUNTLIST_V1 a on a.ACCOUNTID = C.ACCOUNTID
         left join ACCOUNTLIST_V1 t on t.ACCOUNTID = C.toACCOUNTID
         left join CURRENCYFORMATS_V1 cur on cur.currencyid = a.currencyid
         where c.status = 'F'
         order by c.transdate
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest

cron