QIF Export
Posted: Mon Sep 28, 2009 10:44 am
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:
C++ code:
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
^
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();
}