public void ImportFile(UploadedFile file, string dateFormat, bool allowImbalancedTransactions)
{
Method = "import";
Stream s = null;
try {
s = file.Stream();
if (Path.GetExtension(file.Name).ToLower() == ".qif")
{
QifImporter qif = new QifImporter();
new ImportBatchJob(this, qif, delegate() {
try {
Batch.Records = file.Content.Length;
Batch.Status = "Importing file " + file.Name + " as QIF";
Database.BeginTransaction();
qif.DateFormat = dateFormat;
qif.AllowImbalancedTransactions = allowImbalancedTransactions;
qif.Import(new StreamReader(s), this);
Database.Commit();
} catch (Exception ex) {
throw new CheckException(ex, "Error at line {0}\r\n{1}", qif.Line, ex.Message);
} finally {
s.Dispose();
}
Message = "File " + file.Name + " imported successfully as QIF";
});
}
else
{
CsvParser csv = new CsvParser(new StreamReader(s));
Importer importer = Importer.ImporterFor(csv);
Utils.Check(importer != null, "No importer for file {0}", file.Name);
new ImportBatchJob(this, csv, delegate() {
try {
Batch.Records = file.Content.Length;
Batch.Status = "Importing file " + file.Name + " as " + importer.Name + " to ";
Database.BeginTransaction();
importer.DateFormat = dateFormat;
importer.Import(csv, this);
Database.Commit();
} catch (Exception ex) {
throw new CheckException(ex, "Error at line {0}\r\n{1}", csv.Line, ex.Message);
} finally {
s.Dispose();
}
Message = "File " + file.Name + " imported successfully as " + importer.Name + " to " + importer.TableName;
});
}
} catch (Exception ex) {
Log(ex.ToString());
Message = ex.Message;
if (s != null)
{
s.Dispose();
}
}
}
public void ImportFile(UploadedFile file, string dateFormat)
{
Method = "Import";
Stream s = null;
try {
s = file.Stream();
if (Path.GetExtension(file.Name).ToLower() == ".qif") {
QifImporter qif = new QifImporter();
new ImportBatchJob(this, qif, delegate() {
try {
Batch.Records = file.Content.Length;
Batch.Status = "Importing file " + file.Name + " as QIF";
Database.BeginTransaction();
qif.DateFormat = dateFormat;
qif.Import(new StreamReader(s), this);
Database.Commit();
} catch (Exception ex) {
throw new CheckException(ex, "Error at line {0}\r\n{1}", qif.Line, ex.Message);
} finally {
s.Dispose();
}
Message = "File " + file.Name + " imported successfully as QIF";
});
} else {
CsvParser csv = new CsvParser(new StreamReader(s));
Importer importer = Importer.ImporterFor(csv);
Utils.Check(importer != null, "No importer for file {0}", file.Name);
new ImportBatchJob(this, csv, delegate() {
try {
Batch.Records = file.Content.Length;
Batch.Status = "Importing file " + file.Name + " as " + importer.Name + " to ";
Database.BeginTransaction();
importer.DateFormat = dateFormat;
importer.Import(csv, this);
Database.Commit();
} catch (Exception ex) {
throw new CheckException(ex, "Error at line {0}\r\n{1}", csv.Line, ex.Message);
} finally {
s.Dispose();
}
Message = "File " + file.Name + " imported successfully as " + importer.Name + " to " + importer.TableName;
});
}
} catch (Exception ex) {
Log(ex.ToString());
Message = ex.Message;
if (s != null)
s.Dispose();
}
}
/// <summary>
/// User wants to import a statement
/// </summary>
/// <param name="id">Account</param>
/// <param name="format">Statement format (for pasted statement)</param>
/// <param name="data">Pasted statement</param>
/// <param name="file">Uploaded Qif statement</param>
/// <param name="dateFormat">For Qif import</param>
public void StatementImportPost(int id, string format, string data, UploadedFile file, string dateFormat)
{
Account account = Database.Get<Account>(id);
checkAcctType(account.AccountTypeId, AcctType.Bank, AcctType.CreditCard);
JArray result;
DateTime minDate = DateTime.MaxValue;
if (!string.IsNullOrWhiteSpace(file.Content)) {
// They have uploaded a Qif file
QifImporter qif = new QifImporter();
qif.DateFormat = dateFormat;
result = qif.ImportTransactions(new System.IO.StreamReader(file.Stream()), this);
Utils.Check(result.Count > 0, "No transactions found");
minDate = result.Min(i => (DateTime)i["Date"]);
} else {
// They have uploaded pasted data
data = data.Replace("\r", "") + "\n";
Utils.Check(!string.IsNullOrWhiteSpace(format), "You must enter a Statement Format");
// See Import Help for details of format notation
format = format.Replace("\r", "").Replace("\t", "{Tab}").Replace("\n", "{Newline}");
string regex = format
.Replace("{Tab}", @"\t")
.Replace("{Newline}", @"\n");
regex = Regex.Replace(regex, @"\{Any\}", delegate(Match m) {
// Look at next character
string terminator = regex.Substring(m.Index + m.Length, 1);
switch (terminator) {
case @"\n":
case @"\t":
break;
default:
// Terminate "ignore any" section at next newline or tab
terminator = @"\t\n";
break;
}
return @"[^" + terminator + @"]*?";
});
regex = Regex.Replace(regex, @"\{Optional:([^}]+)\}", "(?:$1)?");
regex = Regex.Replace(regex, @"\{([^}]+)\}", delegate(Match m) {
// Look at next character
string terminator = m.Index + m.Length >= regex.Length ? "" : regex.Substring(m.Index + m.Length, 1);
switch (terminator) {
case @"\n":
case @"\t":
break;
default:
// Terminate field at next newline or tab
terminator = @"\t\n";
break;
}
// Create named group with name from inside {}
return @"(?<" + m.Groups[1] + @">[^" + terminator + @"]*?)";
});
regex = "(?<=^|\n)" + regex;
result = new JArray();
Regex r = new Regex(regex, RegexOptions.Singleline);
bool valid = false;
foreach (Match m in r.Matches(data)) {
JObject o = new JObject();
string value = null;
try {
decimal amount = 0;
foreach (string groupName in r.GetGroupNames()) {
value = m.Groups[groupName].Value;
switch (groupName) {
case "0":
break;
case "Date":
DateTime date = DateTime.Parse(value);
if (date < minDate)
minDate = date;
o["Date"] = date;
break;
case "Amount":
Utils.Check(extractAmount(value, ref amount), "Unrecognised Amount {0}", value);
o["Amount"] = -amount;
break;
case "Payment":
if (extractAmount(value, ref amount))
o["Amount"] = -Math.Abs(amount);
break;
case "Deposit":
if (extractAmount(value, ref amount))
o["Amount"] = Math.Abs(amount);
break;
default:
o[groupName] = value;
break;
}
}
Utils.Check(o["Amount"] != null, "No Payment, Deposit or Amount");
Utils.Check(o["Date"] != null, "No Date");
valid = true;
} catch (Exception ex) {
o["@class"] = "warning";
o["Name"] = ex.Message + ":" + value + ":" + m.Value;
}
result.Add(o);
}
if (valid) {
// The format was valid - save it to the account for next time
account.StatementFormat = format;
Database.Update(account);
}
}
JObject record = new JObject().AddRange(
"import", result,
"transactions", potentialMatches(id, minDate)
);
// Save data to session
SessionData.StatementImport = record;
SessionData.Remove("StatementMatch");
Redirect("/banking/statementmatching.html?id=" + id);
}
/// <summary>
/// User wants to import a statement
/// </summary>
/// <param name="id">Account</param>
/// <param name="format">Statement format (for pasted statement)</param>
/// <param name="data">Pasted statement</param>
/// <param name="file">Uploaded Qif statement</param>
/// <param name="dateFormat">For Qif import</param>
public void StatementImportSave(int id, string format, string data, UploadedFile file, string dateFormat)
{
Account account = Database.Get <Account>(id);
checkAcctType(account.AccountTypeId, AcctType.Bank, AcctType.CreditCard,
AcctType.OtherAsset, AcctType.OtherLiability);
JArray result;
DateTime minDate = DateTime.MaxValue;
if (!string.IsNullOrWhiteSpace(file.Content))
{
// They have uploaded a Qif file
QifImporter qif = new QifImporter()
{
DateFormat = dateFormat
};
result = qif.ImportTransactions(new System.IO.StreamReader(file.Stream()), this);
Utils.Check(result.Count > 0, "No transactions found");
minDate = result.Min(i => (DateTime)i["Date"]);
}
else
{
// They have uploaded pasted data
data = data.Replace("\r", "") + "\n";
Utils.Check(!string.IsNullOrWhiteSpace(format), "You must enter a Statement Format");
// See Import Help for details of format notation
format = format.Replace("\r", "").Replace("\t", "{Tab}").Replace("\n", "{Newline}");
string regex = format
.Replace("{Tab}", @"\t")
.Replace("{Newline}", @"\n");
regex = Regex.Replace(regex, @"\{Any\}", delegate(Match m) {
// Look at next character
string terminator = regex.Substring(m.Index + m.Length, 1);
switch (terminator)
{
case @"\n":
case @"\t":
break;
default:
// Terminate "ignore any" section at next newline or tab
terminator = @"\t\n";
break;
}
return(@"[^" + terminator + @"]*?");
});
regex = Regex.Replace(regex, @"\{Optional:([^}]+)\}", "(?:$1)?");
regex = Regex.Replace(regex, @"\{([^}]+)\}", delegate(Match m) {
// Look at next character
string terminator = m.Index + m.Length >= regex.Length ? "" : regex.Substring(m.Index + m.Length, 1);
switch (terminator)
{
case @"\n":
case @"\t":
break;
default:
// Terminate field at next newline or tab
terminator = @"\t\n";
break;
}
// Create named group with name from inside {}
return(@"(?<" + m.Groups[1] + @">[^" + terminator + @"]*?)");
});
regex = "(?<=^|\n)" + regex;
result = new JArray();
Regex r = new Regex(regex, RegexOptions.Singleline);
bool valid = false;
foreach (Match m in r.Matches(data))
{
JObject o = new JObject();
string value = null;
try {
decimal amount = 0;
foreach (string groupName in r.GetGroupNames())
{
value = m.Groups[groupName].Value;
switch (groupName)
{
case "0":
break;
case "Date":
DateTime date = DateTime.Parse(value);
if (date < minDate)
{
minDate = date;
}
o["Date"] = date;
break;
case "Amount":
Utils.Check(extractAmount(value, ref amount), "Unrecognised Amount {0}", value);
o["Amount"] = -amount;
break;
case "Payment":
if (extractAmount(value, ref amount))
{
o["Amount"] = -Math.Abs(amount);
}
break;
case "Deposit":
if (extractAmount(value, ref amount))
{
o["Amount"] = Math.Abs(amount);
}
break;
default:
o[groupName] = value;
break;
}
}
Utils.Check(o["Amount"] != null, "No Payment, Deposit or Amount");
Utils.Check(o["Date"] != null, "No Date");
valid = true;
} catch (Exception ex) {
o["@class"] = "warning";
o["Name"] = ex.Message + ":" + value + ":" + m.Value;
}
result.Add(o);
}
if (valid)
{
// The format was valid - save it to the account for next time
account.StatementFormat = format;
Database.Update(account);
}
}
JObject record = new JObject().AddRange(
"id", id,
"import", result,
"transactions", potentialMatches(id, minDate)
);
addMenuOptionstoRecord(record, account);
// Save data to session
SessionData.StatementImport = record;
SessionData.Remove("StatementMatch");
Redirect("/banking/statementmatching.html?from=%2Fbanking%2Fdetail%3Fid%3D" + id);
}
