public static List <BankExportDocumentSelectionViewModel> TryParseSepaXMLDocument(string filePath)
{
using (var db = new FeeStatusesDBContext())
{
XmlReader reader =
XmlReader.Create(
File.OpenRead(filePath));
var serializer =
new XmlSerializer(
typeof(Document));
var obj = (Document)serializer.Deserialize(reader);
var retList = new List <BankExportDocumentSelectionViewModel>();
IQueryable <string> vulkanIds = db.Member.Where(m => m.MustPay && m.Active).Select(m => m.VulkanID);
IEnumerable <Member> members = db.Member.AsEnumerable();
obj.BkToCstmrStmt.Stmt.
SelectMany(s => s.Ntry).
ToList().
Where(n => n.CdtDbtInd == CreditDebitCode.CRDT).
ToList().
ForEach(n =>
{
int?year = null;
string vulkanId = null;
EntryTransaction2 txDtls = n.NtryDtls.First().TxDtls.First();
string @ref = txDtls.RmtInf.Strd.First().CdtrRefInf.Ref;
string refInfo = txDtls.RmtInf.Strd.First().AddtlRmtInf.First();
PartyIdentification32 dbtr = txDtls.RltdPties.Dbtr; // info o placniku
string[] split = @ref.Split('-');
if (
(split.Length == 2 && vulkanIds.Contains(split[1])) ||
(members.Any(m =>
dbtr.Nm.Equals
(
m.NameUpperWOSumniki +
" " +
m.SurnameUpperWOSumniki
)
||
dbtr.Nm.Equals
(
m.SurnameUpperWOSumniki +
" " +
m.NameUpperWOSumniki
))
)
)
{
string subRef = @ref.Substring(4); // remove SIXX
if (subRef.Contains('-'))
{
year = int.Parse(subRef.Split('-')[0]);
vulkanId = subRef.Split('-')[1];
}
retList.Add(new BankExportDocumentSelectionViewModel
{
Member = vulkanId,
Years =
year.HasValue
? year.Value.ToString(CultureInfo.InvariantCulture)
: "",
BankDocData =
dbtr.Nm + "; " + refInfo + "; " + n.Amt.Value + "€",
Warning =
n.Amt.Value > 10 ||
vulkanId == null ||
!year.HasValue,
Selected =
vulkanId != null &&
year.HasValue &&
n.Amt.Value == 10
});
}
}
);
return(retList);
}
}
private void ProcessDocument(Document document)
{
statements = new List <CamtStatement>();
AccountStatement2[] stmts = document.BkToCstmrStmt.Stmt;
foreach (AccountStatement2 accStatement in stmts)
{
CamtStatement stmt = new CamtStatement();
stmt.Id = accStatement.Id;
stmt.ElctrncSeqNb = accStatement.ElctrncSeqNb.ToString();
object accReportAccount = accStatement.Acct?.Id?.Item;
if (accReportAccount is string)
{
stmt.AccountCode = (string)accReportAccount;
}
else if (accReportAccount is GenericAccountIdentification1)
{
stmt.AccountCode = ((GenericAccountIdentification1)accReportAccount).Id;
}
stmt.BankCode = accStatement.Acct?.Svcr?.FinInstnId?.BIC;
stmt.Currency = accStatement.Acct?.Ccy;
stmt.SeveralYears = false;
string nm = accStatement.Acct?.Ownr?.Nm;
string ownName = nm ?? "AccountNameFor" + stmt.BankCode + "/" + stmt.AccountCode;
CashBalance3[] balances = accStatement.Bal;
if (balances != null)
{
foreach (CashBalance3 balance in balances)
{
// PRCD: PreviouslyClosedBooked
if (balance.Tp?.CdOrPrtry?.Item?.ToString() == "PRCD")
{
stmt.StartBalance = balance.Amt.Value;
// CreditDebitIndicator: CRDT or DBIT for credit or debit
if (balance.CdtDbtInd == CreditDebitCode.DBIT)
{
stmt.StartBalance *= -1.0m;
}
stmt.StartDate = balance.Dt.Item;
}
// CLBD: ClosingBooked
else if (balance.Tp?.CdOrPrtry?.Item?.ToString() == "CLBD")
{
stmt.EndBalance = balance.Amt.Value;
// CreditDebitIndicator: CRDT or DBIT for credit or debit
if (balance.CdtDbtInd == CreditDebitCode.DBIT)
{
stmt.EndBalance *= -1.0m;
}
stmt.EndDate = balance.Dt.Item;
}
// ITBD: InterimBooked
// CLAV: ClosingAvailable
// FWAV: ForwardAvailable
}
}
//string strDiffBalance = "DiffBalanceFor" + stmt.bankCode + "/" + stmt.accountCode;
//Decimal DiffBalance = 0.0m;
//if (Decimal.TryParse(strDiffBalance, out DiffBalance))
//{
// stmt.startBalance += DiffBalance;
// stmt.endBalance += DiffBalance;
//}
//else
//{
// Log.Write("problem parsing decimal from configuration setting DiffBalanceFor" + stmt.bankCode + "/" + stmt.accountCode);
//}
//string filenameWithoutExtension = Path.GetFileNameWithoutExtension(filename);
//// if the file has already been split and moved, use the statement date from the file name (if it is on the last of december)
//if (!filenameWithoutExtension.Contains("_C53_")
// && filenameWithoutExtension.EndsWith("1231")
// && stmt.date.Month != 12
// && stmt.date.Day != 31)
//{
// stmt.date = new DateTime(stmt.date.Year - 1, 12, 31);
//}
ReportEntry2[] entries = accStatement.Ntry;
if (entries != null)
{
foreach (ReportEntry2 entry in entries)
{
if (entry.Amt.Ccy != stmt.Currency)
{
throw new Exception("transaction currency " + entry.Amt.Ccy + " does not match the bank statement currency");
}
CamtTransaction tr = new CamtTransaction();
tr.Pending = entry.Sts == EntryStatus2Code.PDNG;
if (entry.BookgDt != null)
{
tr.InputDate = entry.BookgDt.Item;
}
if (entry.ValDt != null)
{
tr.ValueDate = entry.ValDt.Item;
if (tr.ValueDate.Year != stmt.StartDate.Year)
{
stmt.SeveralYears = true;
}
}
// Soll/Haben
tr.Amount = entry.Amt.Value;
bool debit = entry.CdtDbtInd == CreditDebitCode.DBIT;
if (debit)
{
tr.Amount *= -1.0m;
}
tr.Storno = entry.RvslInd;
EntryDetails1 entryDetails = entry.NtryDtls?.FirstOrDefault();
EntryTransaction2 txDetails = entryDetails?.TxDtls?.FirstOrDefault();
// Verwendungszweck
if (txDetails?.RmtInf?.Ustrd != null)
{
tr.Description = string.Join(string.Empty, txDetails.RmtInf.Ustrd.Select(s => s?.Trim()));
}
tr.Text = entry.AddtlNtryInf?.Trim();
tr.BankCode = debit ?
txDetails?.RltdAgts?.CdtrAgt?.FinInstnId?.BIC :
txDetails?.RltdAgts?.DbtrAgt?.FinInstnId?.BIC;
tr.PartnerName = debit ?
txDetails?.RltdPties?.Cdtr?.Nm :
txDetails?.RltdPties?.Dbtr?.Nm;
object account = debit ?
txDetails?.RltdPties?.CdtrAcct?.Id?.Item :
txDetails?.RltdPties?.DbtrAcct?.Id?.Item;
if (account is string)
{
tr.AccountCode = (string)account;
}
else if (account is GenericAccountIdentification1)
{
tr.AccountCode = ((GenericAccountIdentification1)account).Id;
}
string CrdtName = txDetails?.RltdPties?.Cdtr?.Nm;
string DbtrName = txDetails?.RltdPties?.Dbtr?.Nm;
if ((CrdtName != null) && (CrdtName != ownName))
{
if ((DbtrName != null) && (DbtrName == ownName))
{
// we are the debitor
}
else if (ownName != string.Empty)
{
// sometimes donors write the project or recipient in the field where the organisation is supposed to be
Log.Write("CrdtName is not like expected: " + tr.Description + " --- " + CrdtName);
}
}
tr.EndToEndId = txDetails?.Refs?.EndToEndId;
tr.MessageId = txDetails?.Refs?.MsgId;
tr.PaymentInformationId = txDetails?.Refs?.PmtInfId;
tr.MandateId = txDetails?.Refs?.MndtId;
tr.ProprietaryRef = txDetails?.Refs?.Prtry?.Ref;
tr.CustomerRef = entry.AcctSvcrRef;
if (txDetails?.BkTxCd.Prtry.Cd != null)
{
// NTRF+177+9310+997
// NSTO+152+00900. look for SEPA Geschäftsvorfallcodes
// see the codes: https://www.hettwer-beratung.de/business-portfolio/zahlungsverkehr/elektr-kontoinformationen-swift-mt-940/
string[] GVCCode = txDetails?.BkTxCd?.Prtry?.Cd?.Split(new char[] { '+' });
if (GVCCode.Length > 0)
{
tr.TransactionTypeId = GVCCode[0];
}
if (GVCCode.Length > 1)
{
tr.TypeCode = GVCCode[1];
}
if (GVCCode.Length > 2)
{
tr.Primanota = GVCCode[2];
}
if (GVCCode.Length > 3)
{
tr.TextKeyAddition = GVCCode[3];
}
}
// for SEPA direct debit batches, there are multiple TxDtls records
if (entryDetails?.TxDtls?.Count() > 1)
{
tr.PartnerName = string.Empty;
tr.Description = string.Format("SEPA Sammel-Basislastschrift mit {0} Lastschriften", entryDetails?.TxDtls?.Count());
}
stmt.Transactions.Add(tr);
Log.Write("count : " + stmt.Transactions.Count.ToString());
}
}
statements.Add(stmt);
}
}
