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;
}
}
