QIF Export

Page 1 of 1
7 posts
QIF Export

Nikolay
MMEX Developer

Posts: 1163
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Hi,

I have started to make QIF export functionality for MMEX.

Data were successfully imported into "Family 2009".

Info about QIF format: http://en.wikipedia.org/wiki/Quicken_Interchange_Format
http://linuxfinances.info/info/financeformats.html

Export sample:
Code: Select all
!Type:Cash
D26/09 2009
T100.01
PКоля
L$Доход
Mtest
^
D28/09 2009
T-200.02
PКоля
LАвто:Расходники
Mtest
^
D28/09 2009
T-123.12
P$Raiffeisen MC
LTransfer
M
^


C++ code:
Code: Select all
void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
   
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
   
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"),
        _("QIF Export"), as);
    if (scd->ShowModal() == wxID_OK)
    {
        wxString acctName = scd->GetStringSelection();
        int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

         wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),
                wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);
        if ( !fileName.IsEmpty() )
        {
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );

            mmBEGINSQL_LITE_EXCEPTION;
            static const char sql[] =
            "SELECT TRANSDATE, "
                   "TRANSCODE, TRANSAMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM CHECKINGACCOUNT_V1 "
            "where ACCOUNTID = ? OR TOACCOUNTID = ?";

         wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

         wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

         text
         //   << wxT("!Account") << endl
         //    << acctName <<  endl
         //    << wxT("TChecking") << endl
         //    << wxT("^") <<  endl
             << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString dateDBString = q1.GetString(wxT("TRANSDATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSCODE"));
                wxString amount = q1.GetString(wxT("TRANSAMOUNT"));
            //int amount = q1.GetDouble(wxT("TRANSAMOUNT"));
            

       /* wxString displayTransAmtString;
      wxString transAmtString;
        if (mmCurrencyFormatter::formatDoubleToCurrencyEdit(amount, displayTransAmtString))
            transAmtString = displayTransAmtString;*/


                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_,
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
            notes.Replace(wxT("\n"),wxT(" "));
                wxString transfer = wxT("");
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                      transfer = wxT("T");
                   }
                }
               if (type == wxT("Withdrawal"))
               amount = wxT("-")+amount;

            text << wxT('D') <<dateString << endl
                << wxT('T') << amount << endl
                << wxT('P') <<payee << endl
                << wxT('L') << categ << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
                << wxT('M') << notes << endl
                //mmReadyDisplayString(mmUnCleanString(trans_[selIndex]->notes_))
                << wxT('^') << endl;
               /* <<payee << delimit << type << delimit << amount
                     << delimit << categ << delimit << subcateg << delimit << transNum
                     << delimit << notes << delimit << transfer << endl;*/
                numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
        }// if fileName.empty();

    }// show Modal
    scd->Destroy();
}
Re: QIF Export

Vadim
MMEX Developer

Posts: 142
Joined: Mon Aug 03, 2009 1:35 am
Don't commit this code! You MUST use parameters for SQL.
Never use "select * from ...", explicitly type all required fields.

Never use '\' for string literals, it's uses mostly in shell scripts but not in C++. Use literals concatenation
"select field1, ... "
"from "
"where ...";

The program should become better but not worse!!!
Re: QIF Export

Nikolay
MMEX Developer

Posts: 1163
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Hi Vadim,

Thank you wery mach for comments. The code has been copy/pasted from other sourses. :D

Now QIF file includes splited transactions:

Code: Select all
!Type:Cash
D26/09 2009
T100.01
PКоля
N
L$Доход
Mtest
^
D27/09 2009
T-200.02
PКоля
N654321
LАвто:Расходники
Mtest
^
D28/09 2009
T-123.12
P$Raiffeisen MC
N
LTransfer
M1 notes  2 notes Should be minus
^
D28/09 2009
T222
PЗП
N
LTransfer
MSalary
^
D28/09 2009
T-60
PКоля
N543
LSplit
Msplit test
SАвто:Ремонт
$-10
SАвто:Топливо
$-50
^


C++ code:
Code: Select all
void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
   
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
   
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"),
        _("QIF Export"), as);
    if (scd->ShowModal() == wxID_OK)
    {
        wxString acctName = scd->GetStringSelection();
        int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

         wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),
                wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);
        if ( !fileName.IsEmpty() )
        {
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );

            mmBEGINSQL_LITE_EXCEPTION;
            /*static const char sql[] =
            "SELECT DATE, "
                   "TRANSACTIONTYPE, AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM alldata "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?"; */
         static const char sql[] =
            "SELECT transid, transdate as DATE, "
                   "transcode as TRANSACTIONTYPE, transamount as AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM checkingaccount_v1 "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?";

         wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

         wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

         text
         //   << wxT("!Account") << endl
         //    << acctName <<  endl
         //    << wxT("TChecking") << endl
         //    << wxT("^") <<  endl
             << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString transid = q1.GetString(wxT("TRANSID"));
            wxString dateDBString = q1.GetString(wxT("DATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSACTIONTYPE"));
                wxString amount = q1.GetString(wxT("AMOUNT"));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_,
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
            notes.Replace(wxT("\n"),wxT(" "));
                //wxString transfer = wxT("");
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                  //    transfer = wxT("T");
                   }
                }
               
            text << wxT('D') <<dateString << endl
                << wxT('T') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << amount << endl
                << wxT('P') << payee << endl
                << wxT('N') << transNum << endl
                << wxT('L') << (categ!=wxT("") ? categ : _("Split")) << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
                << wxT('M') << notes << endl;
            
            if (categ == wxT("") && subcateg == wxT(""))
         {
         static const char sql2[] =
            "SELECT SUBCATEGID, CATEGID, SPLITTRANSAMOUNT "
            "FROM splittransactions_v1 "
            "WHERE TRANSID = ?";

         wxSQLite3Statement st2 = db_->PrepareStatement(sql2);
            st2.Bind(1, transid);
           
         wxSQLite3ResultSet q2 = st2.ExecuteQuery();
         while (q2.NextRow())
            {
                   wxString splitamount = q2.GetString(wxT("SPLITTRANSAMOUNT"));
                   wxString splitcateg = mmDBWrapper::getCategoryName(db_, q2.GetInt(wxT("CATEGID")));
                   wxString splitsubcateg = mmDBWrapper::getSubCategoryName(db_,
                       q2.GetInt(wxT("CATEGID")), q2.GetInt(wxT("SUBCATEGID")));
               text << wxT('S') << splitcateg << (splitsubcateg!=wxT("") ? wxT(":") : wxT("")) << splitsubcateg << endl
                   << wxT('$') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << splitamount << endl;
            }
         }

            text << wxT('^') << endl;
            numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
        }// if fileName.empty();

    }// show Modal
    scd->Destroy();
}
Re: QIF Export

Vadim
MMEX Developer

Posts: 142
Joined: Mon Aug 03, 2009 1:35 am
This variant is much more better! :-)
But I recommend you don't do deep nesting in code. For example, you can destroy dialog immediatly after getting result from it.

wxString acctName;

if (scd->ShowModal() == wxID_OK)
{
acctName = scd->GetStringSelection();
}

scd->Destroy();

if (acctName.isEmpty()) {
return;
}

wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);

if ( fileName.IsEmpty() ) {
return;
}
... rest of code here

This will significally improve readability of code.
Re: QIF Export

Nikolay
MMEX Developer

Posts: 1163
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
void mmExportQIF(wxSQLite3Database* db_)
{
 if (mmDBWrapper::getNumAccounts(db_) == 0)
    {
        mmShowErrorMessage(0, _("No Account available! Cannot Export!"), _("Error"));
        return;
    }
    wxArrayString as;
   
    mmBEGINSQL_LITE_EXCEPTION;
    wxSQLite3ResultSet q1 = db_->ExecuteQuery(g_AccountNameSQL);
    while (q1.NextRow())
    {
        as.Add(q1.GetString(wxT("ACCOUNTNAME")));
    }
    q1.Finalize();
    mmENDSQL_LITE_EXCEPTION;

    wxString delimit = mmDBWrapper::getInfoSettingValue(db_, wxT("DELIMITER"), DEFDELIMTER);
   
    wxSingleChoiceDialog* scd = new wxSingleChoiceDialog(0, _("Choose Account to Export from:"),
        _("QIF Export"), as);


   wxString acctName;

   if (scd->ShowModal() == wxID_OK)
   {
   acctName = scd->GetStringSelection();
   }

   scd->Destroy();

   if (acctName.IsEmpty()) {
   return;
   }

   wxString fileName = wxFileSelector(_("Choose QIF data file to Export"),wxT(""), wxT(""), wxT(""), wxT("*.qif"), wxSAVE | wxOVERWRITE_PROMPT);

   if ( fileName.IsEmpty() ) {
   return;
   }
            wxFileOutputStream output( fileName );
            wxTextOutputStream text( output );
         int fromAccountID = mmDBWrapper::getAccountID(db_, acctName);

            mmBEGINSQL_LITE_EXCEPTION;
         static const char sql[] =
            "SELECT transid, transdate as DATE, "
                   "transcode as TRANSACTIONTYPE, transamount as AMOUNT,  SUBCATEGID, "
                   "CATEGID, PAYEEID, "
                   "TRANSACTIONNUMBER, NOTES, TOACCOUNTID, ACCOUNTID "
            "FROM checkingaccount_v1 "
            "WHERE ACCOUNTID = ? OR TOACCOUNTID = ?";

         wxSQLite3Statement st = db_->PrepareStatement(sql);
            st.Bind(1, fromAccountID);
            st.Bind(2, fromAccountID);

         wxSQLite3ResultSet q1 = st.ExecuteQuery();
            int numRecords = 0;

         text
         //   << wxT("!Account") << endl
         //    << acctName <<  endl
         //    << wxT("TChecking") << endl
         //    << wxT("^") <<  endl
             << wxT("!Type:Cash") << endl;

            while (q1.NextRow())
            {
                wxString transid = q1.GetString(wxT("TRANSID"));
            wxString dateDBString = q1.GetString(wxT("DATE"));
                wxDateTime dtdt = mmGetStorageStringAsDate(dateDBString);
                wxString dateString = mmGetDateForDisplay(db_, dtdt);

                int sid, cid;
                wxString payee = mmDBWrapper::getPayee(db_, q1.GetInt(wxT("PAYEEID")), sid, cid);
                wxString type = q1.GetString(wxT("TRANSACTIONTYPE"));
                wxString amount = q1.GetString(wxT("AMOUNT"));
                wxString transNum = q1.GetString(wxT("TRANSACTIONNUMBER"));
                wxString categ = mmDBWrapper::getCategoryName(db_, q1.GetInt(wxT("CATEGID")));
                wxString subcateg = mmDBWrapper::getSubCategoryName(db_,
                    q1.GetInt(wxT("CATEGID")), q1.GetInt(wxT("SUBCATEGID")));
                wxString notes = mmUnCleanString(q1.GetString(wxT("NOTES")));
            notes.Replace(wxT("\n"),wxT(" "));
                //wxString transfer = wxT("");
                if (type == wxT("Transfer"))
                {
                   int tAccountID = q1.GetInt(wxT("TOACCOUNTID"));
                   int fAccountID = q1.GetInt(wxT("ACCOUNTID"));

                   wxString fromAccount = mmDBWrapper::getAccountName(db_,  fAccountID);
                   wxString toAccount = mmDBWrapper::getAccountName(db_,  tAccountID );

                   if (tAccountID == fromAccountID)
                   {
                      type = wxT("Deposit");
                      payee = fromAccount;
                   }
                   else if (fAccountID == fromAccountID)
                   {
                      type = wxT("Withdrawal");
                      payee = toAccount;
                  //    transfer = wxT("T");
                   }
                }
               
            text << wxT('D') <<dateString << endl
                << wxT('T') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << amount << endl
                << wxT('P') << payee << endl
                << wxT('N') << transNum << endl
                << wxT('L') << (categ!=wxT("") ? categ : _("Split")) << (subcateg!=wxT("") ? wxT(":") : wxT("")) << subcateg << endl
                << wxT('M') << notes << endl;
            
            if (categ == wxT("") && subcateg == wxT(""))
         {
         static const char sql2[] =
            "SELECT SUBCATEGID, CATEGID, SPLITTRANSAMOUNT "
            "FROM splittransactions_v1 "
            "WHERE TRANSID = ?";

         wxSQLite3Statement st2 = db_->PrepareStatement(sql2);
            st2.Bind(1, transid);
           
         wxSQLite3ResultSet q2 = st2.ExecuteQuery();
         while (q2.NextRow())
            {
                   wxString splitamount = q2.GetString(wxT("SPLITTRANSAMOUNT"));
                   wxString splitcateg = mmDBWrapper::getCategoryName(db_, q2.GetInt(wxT("CATEGID")));
                   wxString splitsubcateg = mmDBWrapper::getSubCategoryName(db_,
                       q2.GetInt(wxT("CATEGID")), q2.GetInt(wxT("SUBCATEGID")));
               text << wxT('S') << splitcateg << (splitsubcateg!=wxT("") ? wxT(":") : wxT("")) << splitsubcateg << endl
                   << wxT('$') << (type == wxT("Withdrawal") ? wxT("-"): wxT("")) << splitamount << endl;
            }
         }

            text << wxT('^') << endl;
            numRecords++;
            }
            q1.Finalize();

            wxString msg = wxString::Format(wxT("%d transactions exported"), numRecords);
            mmShowErrorMessage(0, msg, _("Export to QIF"));

            mmENDSQL_LITE_EXCEPTION;
}
Re: QIF Export

Vadim
MMEX Developer

Posts: 142
Joined: Mon Aug 03, 2009 1:35 am
That's OK, but would be better to reformat source code. The indent should be 4 spaces. I personally always use 8 spaces for indent, but this project uses 4. May be I reformat all sources later :-)
Re: QIF Export

Nikolay
MMEX Developer

Posts: 1163
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
The patch.
Attachments
qif_export0.9.5.0.patch.zip
(2.83 KiB) Downloaded 283 times
Page 1 of 1
Who is online

Users browsing this forum: No registered users and 2 guests