[ADDED] AllData view

Page 1 of 1
3 posts
[ADDED] AllData view

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Hi,
I newbee in C++ development, but created a new function - to add view AllData (viewtopic.php?f=12&t=533) to the database.
I still do not know how to do patches.
I'd like to hear comments about code. Is it seem fine?

Added into dbwrapper.cpp
void mmDBWrapper::initDB(wxSQLite3Database* db, wxProgressDialog* pgd, const wxString& fpath)
Code: Select all
...
/* Create AllData view */
   mmDBWrapper::createAllDataView(db);
   pgd->Update(99);
....
void mmDBWrapper::createAllDataView(wxSQLite3Database* db)
{
mmBEGINSQL_LITE_EXCEPTION;
bool exists = db->ViewExists(wxT("ALLDATA"));   
if (!exists)   
{
/* Create AllData View */   
db->ExecuteUpdate(wxT("create view alldata as\
    select CANS.TransID as ID, CANS.TransCode as TransactionType, CANS.TransDate as Date, round(strftime('%Y', CANS.TransDate)) as Year\
   , round(strftime('%m', CANS.TransDate)) as Month, round(strftime('%d', CANS.TransDate)) as Day, CAT.CategName as Category, SUBCAT.SubCategName as Subcategory\
   , ROUND(case CANS.TRANSCODE when 'Withdrawal' then -1*CANS.TRANSAMOUNT  else CANS.TRANSAMOUNT end,2) as Amount \
   , (select cf.BaseConvRate from currencyformats_v1 cf where cf.currencyid=FROMACC.CURRENCYID) as BaseConvRate\
   , FROMACC.CurrencyID as CurrencyID, FROMACC.AccountName as AccountName,  FROMACC.AccountID as AccountID, TOACC.AccountName as ToAccountName, TOACC.ACCOUNTId as ToAccountID, CANS.ToTransAmount as ToTransAmount, TOACC.CURRENCYID as ToCurrencyID\
   , 0 as Splitted , CAT.CategID as CategID, SUBCAT.SubCategID as SubCategID, PAYEE.PayeeName as Payee, PAYEE.PayeeID as PayeeID\
   , CANS.TRANSACTIONNUMBER as TransactionNumber, CANS.Status as Status, CANS.NOTES as Notes\
    from  CHECKINGACCOUNT_V1 CANS\
    inner join CATEGORY_V1    CAT     on CAT.CATEGID      = CANS.CATEGID\
    left  join SUBCATEGORY_V1 SUBCAT  on SUBCAT.SUBCATEGID= CANS.SUBCATEGID\
    left  join PAYEE_V1       PAYEE   on PAYEE.PAYEEID    = CANS.PAYEEID\
    left  join ACCOUNTLIST_V1 FROMACC on FROMACC.ACCOUNTID= CANS.ACCOUNTID\
    left  join ACCOUNTLIST_V1 TOACC   on TOACC.ACCOUNTID  = CANS.TOACCOUNTID\
   UNION ALL\
    SELECT CASS.TRANSID, CASS.TRANSCODE, CASS.TransDate, round(strftime('%Y', CASS.TransDate)) as Year\
   , round(strftime('%m', CASS.TransDate)) as Month, round(strftime('%d', CASS.TransDate)) as Day,CAT.CATEGNAME, SUBCAT.SUBCATEGNAME\
   ,ROUND(SPLIT.SPLITTRANSAMOUNT, 2)*(case when CASS.TRANSCODE='Withdrawal' then -1 else 1 end) as Amount \
   , (select cf.BaseConvRate from currencyformats_v1 cf where cf.currencyid=FROMACC.CURRENCYID) as BaseConvRate\
   , FROMACC.CURRENCYID, FROMACC.ACCOUNTNAME, FROMACC.ACCOUNTId ,TOACC.ACCOUNTNAME, TOACC.ACCOUNTId, CASS.totransamount, TOACC.CURRENCYID \
   , 1 as Splitted , CAT.CATEGId, SUBCAT.SUBCATEGId, PAYEE.PAYEENAME, PAYEE.PAYEEID\
   , CASS.TRANSACTIONNUMBER, CASS.Status, CASS.NOTES \
   from  CHECKINGACCOUNT_V1 CASS\
    inner join SPLITTRANSACTIONS_V1 SPLIT   on SPLIT.TRANSID    = CASS.TRANSID\
    inner join CATEGORY_V1          CAT     on CAT.CATEGID      = SPLIT.CATEGID\
    left  join SUBCATEGORY_V1       SUBCAT  on SUBCAT.SUBCATEGID= SPLIT.SUBCATEGID\
    left  join PAYEE_V1             PAYEE   on PAYEE.PAYEEID    = CASS.PAYEEID\
    left  join ACCOUNTLIST_V1       FROMACC on FROMACC.ACCOUNTID= CASS.ACCOUNTID\
    left  join ACCOUNTLIST_V1       TOACC   on TOACC.ACCOUNTID  = CASS.TOACCOUNTID\
   order by CANS.transid;"));
       exists = db->ViewExists(wxT("ALLDATA"));
       wxASSERT(exists);   

     }
mmENDSQL_LITE_EXCEPTION;
}


Added into dbwrapper.h in class class mmDBWrapper:

Code: Select all
    static void createAllDataView(wxSQLite3Database* db);


Added in wxsqlite3.cpp
Code: Select all
....
bool wxSQLite3Database::ViewExists(const wxString& viewName)
{
  wxSQLite3Statement stmt = PrepareStatement("select count(*) from sqlite_master where type='view' and name like ?");
  stmt.Bind(1, viewName);
  wxSQLite3ResultSet resultSet = stmt.ExecuteQuery();
  long value = 0;
  resultSet.GetAsString(0).ToLong(&value);
  return (value > 0);
.....
}

Added in wx\wxsqlite3.h
Code: Select all
....
  /// Check whether a view with the given name exists
  /**
  * \param viewName name of the view
  * \return TRUE if the view exists, FALSE otherwise
  */
  bool ViewExists(const wxString& viewName);
...
Re: New Feature: AllData view

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
The Patch uploaded.
Attachments
AllData_View.zip
(1.87 KiB) Downloaded 244 times
Re: [ADDED] AllData view

madhan
Site Admin

Posts: 242
Joined: Sun Nov 30, 2008 2:06 pm
Thanks Nikolay! Checked in patch into SVN 396. Please verify your changes in SVN.
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 1 guest