private void button3_Click(object sender, EventArgs e) { DialogResult result = openFileDialog2.ShowDialog(); if (result == DialogResult.OK) // Test result. { var testexample = new EXANTE_Entities(); var reader = new StreamReader(openFileDialog2.FileName); var allfromfile = new List<CpTrade>(); //Ticket Ref Party Type Symbol B/S Amount Currency Rate Counter Amount Currency Tenor Value Date Ticket Creation Order Ref GRID //EOD SWAP 201311190000/1127 FAR LEG 60002000000 NZDUSD Sell 15 857.00 NZD 0.83218 13 195.88 USD SPOT 21/11/2013 19/11/2013 06:18:55 var lineFromFile = reader.ReadLine(); TradesParserStatus.Text = "Processing"; var reportDate = openFileDialog2.FileName.Substring(openFileDialog2.FileName.IndexOf("_") + 1, openFileDialog2.FileName.LastIndexOf("-") - openFileDialog2.FileName.IndexOf("_") - 1); int idTradeDate = 13, idSymbol = 3, idQty = 5, idSide = 4, idPrice = 7, idValueDate = 12, idValue = 9, idType = 11; IFormatProvider theCultureInfo = new System.Globalization.CultureInfo("en-GB", true); var minDate = Convert.ToDateTime(reportDate); while (!reader.EndOfStream) { lineFromFile = reader.ReadLine().Replace("\"", ""); var rowstring = lineFromFile.Split(','); if (rowstring[1] != "") { var tradedate = Convert.ToDateTime(rowstring[idTradeDate], theCultureInfo); var qty = rowstring[idSide].IndexOf("Buy") == -1 ? Convert.ToDouble(rowstring[idQty].Replace(" ", ""))*(-1) : Convert.ToDouble(rowstring[idQty].Replace(" ", "")); var ValueDate = Convert.ToDateTime(rowstring[idValueDate], theCultureInfo); allfromfile.Add(new CpTrade { ReportDate = Convert.ToDateTime(reportDate), TradeDate = tradedate, BrokerId = "ADSSOREX", Symbol = rowstring[idSymbol], Type = rowstring[idType], Qty = qty, Price = Convert.ToDouble(rowstring[idPrice].Replace(" ", "")), ValueDate = Convert.ToDateTime(rowstring[idValueDate], theCultureInfo), cp_id = 19, ExchangeFees = null, Fee = null, Id = null, BOSymbol = null, BOTradeNumber = null, value = Convert.ToDouble(rowstring[idValue].Replace(" ", "")), Timestamp = DateTime.UtcNow, valid = 1, username = "******", // FullId = null, BOcp = null, exchangeOrderId = rowstring[idSymbol]+qty.ToString()+rowstring[idPrice].Replace(" ", "") }); if ((rowstring[idType]=="Spot") && (tradedate < minDate)) minDate = tradedate; } } var nextdate = Convert.ToDateTime(reportDate); var startdate = new DateTime(minDate.Year,minDate.Month,minDate.Day,0,0,0); var queryable = from ct in testexample.Ctrades where ct.Date >=startdate && ct.Date < (nextdate) && ct.cp_id == "ADSS_V2" select new { ct.trade_id, ct.tradeNumber, ct.qty, ct.price, ct.symbol_id, ct.fullid, ct.RecStatus }; var botrades = new Dictionary<string, List<BOtrade>>(); var n = queryable.Count(); foreach (var ctrade in queryable) { var ctrade_id = ctrade.symbol_id.Replace(".EXANTE","")+ctrade.qty.ToString()+ctrade.price.ToString(); ctrade_id = ctrade_id.Replace("/", ""); var tempBotrade = new BOtrade { TradeNumber = (long)ctrade.tradeNumber, Qty = (double)ctrade.qty, Price = (double)ctrade.price, symbol = ctrade.symbol_id, ctradeid = ctrade.fullid, RecStatus = ctrade.RecStatus }; if (botrades.ContainsKey(ctrade_id)) { botrades[ctrade_id].Add(tempBotrade); } else botrades.Add(ctrade_id, new List<BOtrade> { tempBotrade }); //tempBotrade}); } var recon = Reconciliation(allfromfile, botrades, "exchangeOrderId", "2"); foreach (var botrade in botrades){ foreach (var botradeItemlist in botrade.Value){ if (botradeItemlist.RecStatus){ using (var data = new EXANTE_Entities()){ data.Database.ExecuteSqlCommand("UPDATE Ctrades Set RecStatus ={0} WHERE fullid = {1}", true, botradeItemlist.ctradeid); } } } } foreach (CpTrade tradeIndex in allfromfile) { testexample.CpTrades.Add(tradeIndex); } testexample.SaveChanges(); foreach (Reconcilation reconitem in recon) { reconitem.CpTrade_id = allfromfile[(int) reconitem.CpTrade_id].FullId; testexample.Reconcilations.Add(reconitem); } testexample.SaveChanges(); } }
private void button2_Click(object sender, EventArgs e) { if (openFileDialog1.ShowDialog() == DialogResult.OK) { //Создаём приложение. TradesParserStatus.Text = "Processing"; Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application(); //Открываем книгу. Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog1.FileName, 0, false, 5, "", "", false, Microsoft.Office.Interop .Excel.XlPlatform .xlWindows, "", true, false, 0, true, false, false); //Выбираем таблицу(лист). Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet; ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ObjWorkBook.Sheets["Derivative Trades_Деривативы"]; Microsoft.Office.Interop.Excel.Range xlRange = ObjWorkSheet.UsedRange; int rowCount = xlRange.Rows.Count+1; int colCount = xlRange.Columns.Count; DateTime reportdate = DateTime.FromOADate(xlRange.Cells[3, 8].value2); // reportdate = reportdate.AddDays(-1); var testexample = new EXANTE_Entities(); var nextdate = AtonDate.Value.AddDays(1); var queryable = from ct in testexample.Ctrades where ct.Date >= reportdate && ct.Date < (nextdate) && ct.cp_id == "FORTS_TR" select new {ct.trade_id, ct.tradeNumber,ct.qty, ct.price, ct.symbol_id, ct.fullid, ct.RecStatus}; var botrades = new Dictionary<string, List<BOtrade>>(); var n = queryable.Count(); foreach (var ctrade in queryable) { var ctrade_id = ctrade.trade_id.Replace("DC:F:", ""); var tempBotrade = new BOtrade { TradeNumber = (long) ctrade.tradeNumber, Qty = (double) ctrade.qty, Price = (double) ctrade.price, symbol = ctrade.symbol_id, ctradeid = ctrade.fullid, RecStatus = ctrade.RecStatus }; if (botrades.ContainsKey(ctrade_id)) { botrades[ctrade_id].Add(tempBotrade); } else botrades.Add(ctrade_id, new List<BOtrade> {tempBotrade}); //tempBotrade}); } var allfromfile = new List<CpTrade>(); for (int i = 10; i < rowCount; i++) { if (xlRange.Cells[i, 4].value2 != null) { var tradeDate = DateTime.FromOADate(xlRange.Cells[i, 4].value2); if (tradeDate.Date==reportdate.Date) { var time = DateTime.FromOADate(xlRange.Cells[i, 5].value2); var ts = new TimeSpan(time.Hour, time.Minute, time.Second); tradeDate = tradeDate.Date + ts; allfromfile.Add(new CpTrade { ReportDate = reportdate, TradeDate = tradeDate, BrokerId = "Aton", Symbol = xlRange.Cells[i, 10].value2, Type = "FUTURES", Qty = xlRange.Cells[i, 6].value2.IndexOf("Buy") == -1 ? Convert.ToInt64(xlRange.Cells[i, 11].value2)*(-1) : Convert.ToInt64(xlRange.Cells[i, 11].value2), Price = xlRange.Cells[i, 12].value2, ValueDate = null, cp_id = 2, ExchangeFees = xlRange.Cells[i, 19].value2 - xlRange.Cells[i, 16].value2, Fee = 0, Id = null, BOSymbol = null, BOTradeNumber = null, value = xlRange.Cells[i, 16].value2, Timestamp = DateTime.UtcNow, valid = 1, username = "******", // FullId = null, BOcp = null, exchangeOrderId = Convert.ToString(xlRange.Cells[i, 2].value2) }); } } } var recon = Reconciliation(allfromfile, botrades, "exchangeOrderId", "2"); foreach (var botrade in botrades){ foreach (var botradeItemlist in botrade.Value){ if (botradeItemlist.RecStatus){ using (var data = new EXANTE_Entities()){ data.Database.ExecuteSqlCommand("UPDATE Ctrades Set RecStatus ={0} WHERE fullid = {1}", true, botradeItemlist.ctradeid); } } } } foreach (CpTrade tradeIndex in allfromfile) { testexample.CpTrades.Add(tradeIndex); } testexample.SaveChanges(); foreach (Reconcilation reconitem in recon) { reconitem.CpTrade_id = allfromfile[(int) reconitem.CpTrade_id].FullId; testexample.Reconcilations.Add(reconitem); } testexample.SaveChanges(); testexample.Dispose(); ObjExcel.Quit(); TradesParserStatus.Text = "Done:"+openFileDialog1.FileName; } }