I newbee in C++ development, but created a new function - to add view AllData (http://www.codelathe.com/forum/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;
}
Code: Select all
static void createAllDataView(wxSQLite3Database* db);
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);
.....
}
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);
...