/************************************************************************/
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="OfxResponseBase"/> class.
/// </summary>
/// <param name="header">The Ofx header object.</param>
/// <param name="xmlDoc">The Xml document</param>
protected OfxResponseBase(OfxHeader header, XmlDocument xmlDoc)
{
ValidateNull(header, "OfxDocumentBase.Header");
ValidateNull(xmlDoc, "OfxDocumentBase.XmlDoc");
Header = header;
SignOnResponse = new SignOnResponse(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(SignOnResponse))));
}
public int SelectOneIdOfxHeader(OfxHeader ofxHeader)
{
string sql = "select ID from OFX_HEADER";
sql += $" where OFXHEADER = {ofxHeader.Ofxheader} and DATA = '{ofxHeader.Data}' and VERSION = '{ofxHeader.Version}' and SECURITY = '{ofxHeader.Security}' and ENCODING = '{ofxHeader.Encoding}' and [CHARSET] = '{ofxHeader.Charset}' and COMPRESSION = '{ofxHeader.Compression}' and OLDFILEUID = '{ofxHeader.Oldfileuid}' and NEWFILEUID = '{ofxHeader.Newfileuid}' ";
int id = 0;
using (var conn = new SqlConnection(stringConexao))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (var comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = sql;
SqlDataReader rdr = comm.ExecuteReader();
while (rdr.Read())
{
id = Convert.ToInt32(rdr["ID"]);
break;
}
}
}
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(id);
}
public void Insert(OfxHeader model)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
using (var conn = new SqlConnection(stringConexao))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (var comm = new SqlCommand())
{
comm.Connection = conn;
string sql = "INSERT INTO [dbo].[OFX_HEADER]([OFXHEADER],[DATA],[VERSION],[SECURITY],[ENCODING],[CHARSET],[COMPRESSION],[OLDFILEUID],[NEWFILEUID])";
sql += $" VALUES({model.Ofxheader},'{model.Data}','{model.Version}','{model.Security}','{model.Encoding}','{model.Charset}','{model.Compression}','{model.Oldfileuid}','{model.Newfileuid}')";
sql += " SELECT SCOPE_IDENTITY()";
comm.CommandText = sql;
var result = comm.ExecuteScalar();
int idOfxHeader = Convert.ToInt32(result ?? 0);
int idSonrsStatus = InsertStatus(model.OfxBody.Signonmsgsrsv1.Sonrs.SonrsStatus, conn);
int idSonrs = InsertSonrs(model.OfxBody.Signonmsgsrsv1.Sonrs, idSonrsStatus, conn);
int idLedgerbal = InsertLedgerbal(model.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Ledgerbal, conn);
int idStmttrnrsStatus = InsertStatus(model.OfxBody.Bankmsgsrsv1.Stmttrnrs.Status, conn);
int idBanktranlist = InsertBanktran(model.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist, conn);
int idBankacctfrom = InsertBankacctfrom(model.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Bankacctfrom, conn);
int idStmtrs = InsertStmtrs(model.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs, idBanktranlist, idBankacctfrom, idLedgerbal, conn);
int idStmttrnrs = InsertStmttrnrs(model.OfxBody.Bankmsgsrsv1.Stmttrnrs, idStmtrs, idStmttrnrsStatus, conn);
int idOfxBody = InsertOfxBody(idSonrs, idOfxHeader, idStmttrnrs, conn);
}
}
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
scope.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Creates a <see cref="OfxProfileResponse"/> object from the specified Ofx response string.
/// </summary>
/// <param name="inputStr">The input string.</param>
/// <returns>An OfxProfileResponse object.</returns>
public static OfxProfileResponse Create(string inputStr)
{
OfxHeader header = new OfxHeader(inputStr);
string xmlStr = SgmlToXml(inputStr.Substring(header.HeaderEnd));
var doc = new XmlDocument();
doc.Load(new StringReader(xmlStr));
OfxProfileResponse ofxProfileResponse = new OfxProfileResponse(header, doc);
return(ofxProfileResponse);
}
/// <summary>
/// Creates a <see cref="OfxResponse"/> object from the specified Ofx response string.
/// </summary>
/// <param name="inputStr">The input string.</param>
/// <param name="messageSetVersion">The message set version to use.</param>
/// <returns>An OfxResponse object.</returns>
public static OfxResponse Create(string inputStr, int messageSetVersion)
{
OfxHeader header = new OfxHeader(inputStr);
string xmlStr = SgmlToXml(inputStr.Substring(header.HeaderEnd));
var doc = new XmlDocument();
doc.Load(new StringReader(xmlStr));
OfxResponse ofxResponse = new OfxResponse(header, doc, messageSetVersion);
return(ofxResponse);
}
/************************************************************************/
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="OfxProfileResponse"/> class.
/// </summary>
/// <param name="header">The Ofx header object.</param>
/// <param name="xmlDoc">The Xml document</param>
public OfxProfileResponse(OfxHeader header, XmlDocument xmlDoc) : base(header, xmlDoc)
{
ProfileDate = GetDateTimeValue(xmlDoc.FirstChild, nameof(ProfileDate));
Bank = new BankAccountProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(Bank))));
BillPay = new BillPayProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(BillPay))));
CreditCard = new AccountProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(CreditCard))));
Email = new EmailProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(Email))));
Profile = new ProfileData(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(Profile))));
SignOn = new ProfileData(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(SignOn))));
SignUp = new ProfileData(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(SignUp))));
SignonInfo = new SignonInfoProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(SignonInfo))));
Institution = new InstitutionProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(Institution))));
Investment = new InvestmentProfile(GetNestedNode(xmlDoc.FirstChild, GetNodeName(nameof(Investment))));
}
public ActionResult Index(HttpPostedFileBase[] files)
{
OfxHeader ofxHeader = new OfxHeader();
ViewBag.isExistSomething = false;
ViewBag.FilesExist = "Os seguintes arquivos já foram importados";
if (ModelState.IsValid)
{
for (int i = 0; i < files.Length; i++)
{
HttpPostedFileBase file = files[i];
if (file != null)
{
var InputFileName = Path.GetFileName(file.FileName);
var ServerSavePath = Path.Combine(Server.MapPath("~/FilesOFX/") + InputFileName);
if (System.IO.File.Exists(ServerSavePath))
{
ViewBag.FilesExist += "<br><br>" + InputFileName;
ViewBag.isExistSomething = true;
}
else
{
file.SaveAs(ServerSavePath);
ViewBag.UploadStatus = files.Count().ToString() + " files uploaded successfully.";
ParseFromOFXToObecjt obj = new ParseFromOFXToObecjt();
OfxHeader result = obj.Parse(ServerSavePath);
if (i == 0)
{
ofxHeader = result;
}
else
{
ofxHeader.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns.AddRange(result.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns);
}
}
}
}
if (ofxHeader != null && ofxHeader.OfxBody != null)
{
OFXHeaderRepository ofx = new OFXHeaderRepository();
ofxHeader.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns = ofxHeader.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns.OrderBy(x => x.Trntype).ToList();
int idOfxHeader = ofx.SelectOneIdOfxHeader(ofxHeader);
if (idOfxHeader > 0)
{
ofx.Insert(ofxHeader);
}
var Stmttrn = ofx.GetById(idOfxHeader);
ViewBag.Stmttrn = Stmttrn;
float countDebit = ofxHeader.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns
.Where(x => x.Trntype == "DEBIT").Select(x => x.Trnamt).Sum();
float countCredit = ofxHeader.OfxBody.Bankmsgsrsv1.Stmttrnrs.Stmtrs.Banktranlist.Stmttrns
.Where(x => x.Trntype == "CREDIT").Select(x => x.Trnamt).Sum();
ViewBag.CountDebit = countDebit.ToString("C2", CultureInfo.CurrentCulture);
ViewBag.CountCredit = countCredit.ToString("C2", CultureInfo.CurrentCulture);
ViewBag.AccountBalance = (countCredit - countDebit).ToString("C2", CultureInfo.CurrentCulture);
}
}
return(View());
}
/************************************************************************/
#region Constructor (internal)
/// <summary>
/// Initializes a new instance of the <see cref="OfxResponse"/> class.
/// </summary>
/// <param name="header">The header object</param>
/// <param name="xmlDoc">The Xml document</param>
/// <param name="messageSetVersion">The message set version to use.</param>
internal OfxResponse(OfxHeader header, XmlDocument xmlDoc, int messageSetVersion) : base(header, xmlDoc)
{
Bank = new BankMessageSet(xmlDoc, messageSetVersion);
Investment = new InvestmentMessageSet(xmlDoc, messageSetVersion);
Information = new InformationMessageSet(xmlDoc, messageSetVersion);
}
