public Form1()
{
InitializeComponent();
ConnectionStringSettingsCollection connection = ConfigurationManager.ConnectionStrings;
for (int i = 0; i < connection.Count; i++)
{
if (connection[i].ProviderName != "")
{
comboBoxEnviroment.Items.Add(connection[i].Name);
if (connection[i].Name == "EXANTE_Entities")
{
comboBoxEnviroment.Text = "EXANTE_Entities";
}
}
}
_currentConnection = comboBoxEnviroment.Text;
var db = new EXANTE_Entities(_currentConnection);
List<DBBORecon_mapping> brockerlist = (from rec in db.DBBORecon_mapping
where rec.valid == 1
select rec).ToList();
foreach (DBBORecon_mapping t in brockerlist)
{
BrockerComboBox.Items.Add(t.NameProcess);
if (t.NameProcess == "ADSS-ADSS")
{
BrockerComboBox.Text = "ADSS-ADSS";
_currentAcc = "ADSS-ADSS";
}
}
db.Dispose();
}
private List<InitialTrade> OpenParsing(string cp, string identify)
{
DialogResult result = openFileDialog2.ShowDialog();
var lInitTrades = new List<InitialTrade>();
if (result == DialogResult.OK) // Test result.
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start " + cp + " trades uploading");
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, ColumnMapping> cMapping = (from ct in db.ColumnMappings
where
ct.Brocker == cp && ct.FileType == "EXCEL" &&
ct.Account == identify
select ct).ToDictionary(k => k.Type, k => k);
//if (cMapping["FU"].cTabName == null || CheckTabExist(openFileDialog2.FileName, cMapping["FU"].cTabName))removeOverallRows(openFileDialog2.FileName, cMapping["FU"].cTabName, cMapping["FU"].cLineStart);
List<InitialTrade> inittrades;
if (cMapping.ContainsKey("ST") && cMapping["ST"].Brocker != "Renesource")
{
inittrades = ParseBrockerExcelToCpTrade(openFileDialog2.FileName, cMapping["ST"]);
if (inittrades != null) lInitTrades.AddRange(inittrades);
}
/* if (cMapping.ContainsKey("FX"))
{
inittrades = ParseBrockerExcelToCpTrade(openFileDialog2.FileName, cMapping["FX"]);
if (inittrades != null) lInitTrades.AddRange(inittrades);
}*/
if (cMapping.ContainsKey("FU"))
{
inittrades = ParseBrockerExcelToCpTrade(openFileDialog2.FileName, cMapping["FU"]);
if (inittrades != null)
{
foreach (InitialTrade initialTrade in inittrades)
{
initialTrade.ccy = "RUR";
if (cp == "OPEN")
{
initialTrade.Account = "UEX6678";
}
else
{
if (cp == "Renesource")
{
initialTrade.Account = "RUFO0288";
initialTrade.value = -Math.Sign((long) initialTrade.Qty)*initialTrade.value;
if (initialTrade.Type == "FUT") initialTrade.Type = "FU";
if (initialTrade.Type == "OPT") initialTrade.Type = "OP";
}
else
{
if (cp == "ITInvest")
{
initialTrade.Account = "BC16686-MO-01";
initialTrade.TypeOfTrade = "Trade";
}
}
}
}
lInitTrades.AddRange(inittrades);
}
}
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + cp + " trades uploading completed." +
(TimeEnd - TimeStart).ToString());
return lInitTrades;
}
else return lInitTrades;
}
private void ABNReconButtonClick(object sender, EventArgs e)
{
var reportdate = ABNDate.Value;//todo Get report date from xml Processing date
var testexample = new EXANTE_Entities();
var symbolmap = getMap("ABN");
if (noparsingCheckbox.Checked)
{
var nextdate = reportdate.AddDays(1);
var cptradefromDb = from cptrade in testexample.CpTrades
where
cptrade.TypeOfTrade == "01" && cptrade.valid == 1 && cptrade.BrokerId=="ABN" &&
cptrade.ReportDate >= reportdate.Date && cptrade.ReportDate < (nextdate.Date) &&
cptrade.BOTradeNumber == null
select cptrade;
var cptradelist = cptradefromDb.ToList();
foreach (CpTrade cpTrade in cptradelist)
{
if (cpTrade.BOSymbol == null)
{
Map symbolvalue;
if (cpTrade.Type == "FW")
{
var t = 1;
}
var key = cpTrade.Symbol + cpTrade.Type;
if (cpTrade.Type == "FU") key = key + cpTrade.ValueDate.Value.ToShortDateString();
if (symbolmap.TryGetValue(key, out symbolvalue))
{
cpTrade.BOSymbol = symbolvalue.BOSymbol;
}
if (cpTrade.Type == "FW") cpTrade.BOSymbol = cpTrade.BOSymbol + cpTrade.ValueDate.Value.ToShortDateString();
var tt = "GBP/USD.E2.23M2014";
var p=tt.IndexOf('.',tt.IndexOf('.')+1);// > -1)
}
}
AbnRecon(reportdate, cptradelist);
}
else
{
var allfromfile = new List<CpTrade>();
var futtrades = new List<CpTrade>();
var result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK)
{
if (CliffCheckBox.Checked)
{
var cliffdict = LoadCliff(openFileDialog2.FileName, reportdate);
List<string> rowlist;
// if (cliffdict.TryGetValue("610", out rowlist)) updateBalance(rowlist, reportdate);
// if (cliffdict.TryGetValue("310", out rowlist))allfromfile = ExtractTradesFromCliff(rowlist, symbolmap);
// if (cliffdict.TryGetValue("410", out rowlist))allfromfile.AddRange(ExtractTradesFromCliff(rowlist, symbolmap));
if (cliffdict.TryGetValue("210", out rowlist)) allfromfile.AddRange(ExtractOptionTradesFromCliff(rowlist, symbolmap));
}
else
{
allfromfile = ExtractTradesFromXml(symbolmap);
}
foreach (CpTrade tradeIndex in allfromfile)
{
testexample.CpTrades.Add(tradeIndex);
}
testexample.SaveChanges();
allfromfile = allfromfile.Where(s => s.TypeOfTrade == "01").ToList();
AbnRecon(reportdate, allfromfile);
}
}
}
private List<Array> getABNMapping(string filter)
{
var mapping = new List<Array>();
var testexample =new EXANTE_Entities();
var mappings = from map in testexample.Mappings
where map.valid == 1
select map;
var dictMap = new List<Array>();// mappings.ToList();
return dictMap;
}
private void AbnRecon(DateTime reportdate, List<CpTrade> trades)
{
var cplist = new List<string> {"LEK", "CQG", "FASTMATCH", "CURRENEX","EXANTE", ""};
var mltytrades = MultyTradesCheckBox.Checked;
var boTradeslist = CreateIdForBoTrades(getTradesFromDB(reportdate, cplist, true));
var numberBoTrades = boTradeslist.Count;
var cpmapping = getBOtoABNMapping();
var symbolMap = getSymbolMap();
var abnTradeslist = CreateIdForAbnTrades(getOnlyTrades(trades));
var recon = new List<Reconcilation>();
var db = new EXANTE_Entities();
foreach (var cpTrade in abnTradeslist)
{
List<Ctrade> ctrade;
if (boTradeslist.TryGetValue(cpTrade.Id, out ctrade))
{
cpTrade.BOTradeNumber = ctrade[0].tradeNumber.ToString();
cpTrade.BOcp = ctrade[0].cp_id;
cpTrade.Comment = ctrade[0].BOtradeTimestamp.Value.ToShortDateString();
ctrade[0].RecStatus = true;
db.CpTrades.Attach(cpTrade);
db.Entry(cpTrade).State = EntityState.Modified;
db.Ctrades.Attach(ctrade[0]);
db.Entry(ctrade[0]).State = EntityState.Modified;
ctrade.RemoveAt(0);
if (ctrade.Count == 0)
{
boTradeslist.Remove(cpTrade.Id);
}
recon.Add(new Reconcilation
{
CpTrade_id = cpTrade.FullId,
Ctrade_id = Convert.ToInt64(cpTrade.BOTradeNumber),
Timestamp = DateTime.UtcNow,
username = "******",
valid = 1
});
}
else {
var t = 1;
// CheckMultitrades(cpTrade,boTradeslist.Values.SelectMany(x=>x).ToList());
}
}
db.SaveChanges();
/* List<Ctrade> bolist = null;
foreach (KeyValuePair<string, List<Ctrade>> keyValuePair in boTradeslist)
{
if ((keyValuePair.Value[0].RecStatus == false)&&(keyValuePair.Value[0].symbol_id.Contains("%/%.%.%20%")))
{
var t = 1;
}
bolist.Add(keyValuePair.Value[0]);
}*/
/* foreach (CpTrade cpTrade in abnTradeslist)
{
db.CpTrades.Attach(cpTrade);
db.Entry(cpTrade).State = EntityState.Modified;
}*/
foreach (Reconcilation reconcilation in recon)
{
db.Reconcilations.Add(reconcilation);
}
db.SaveChanges();
}
private Array getBOtoABNMapping(){
var testexample = new EXANTE_Entities();
var queryable =
from ct in testexample.Mappings
where ct.valid == 1 && ct.Type == "Cp"
select new {ct.BrockerSymbol,ct.BOSymbol};
return queryable.ToArray();
}
private void button4_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>();
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 = 4,
idQty = 6,
idSide = 5,
idPrice = 8,
idValueDate = 12,
idValue = 9;
IFormatProvider theCultureInfo = new System.Globalization.CultureInfo("en-GB", true);
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine().Replace("\"", "");
var rowstring = lineFromFile.Split(Delimiter);
if (rowstring[1] != "")
{
allfromfile.Add(new CpTrade
{
ReportDate = Convert.ToDateTime(reportDate),
TradeDate = Convert.ToDateTime(rowstring[idTradeDate], theCultureInfo),
BrokerId = "ADSSOREX",
Symbol = rowstring[idSymbol],
Type = "FX",
Qty = rowstring[idSide].IndexOf("Buy") == -1
? Convert.ToDouble(rowstring[idQty].Replace(" ", "")) * (-1)
: Convert.ToDouble(rowstring[idQty].Replace(" ", "")),
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 = null
});
}
}
foreach (CpTrade tradeIndex in allfromfile)
{
testexample.CpTrades.Add(tradeIndex);
}
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;
}
}
private void RjoClick(object sender, EventArgs e)
{
DateTime reportdate = ABNDate.Value; //todo Get report date from xml Processing date
var db = new EXANTE_Entities(_currentConnection);
if (!noparsingCheckbox.Checked)
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start RJO trades uploading");
List<InitialTrade> LInitTrades = TradeParsing("RJO", "CSV", "FU", "Main");
List<CpTrade> lCptrades = InitTradesConverting(LInitTrades, "RJO");
foreach (CpTrade cptrade in lCptrades)
{
db.CpTrades.Add(cptrade);
}
SaveDBChanges(ref db);
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "RJO trades uploading completed." +
(TimeEnd - TimeStart).ToString());
}
else
{
DateTime nextdate = reportdate.AddDays(1);
Dictionary<string, Map> symbolmap = getMapping("RJO");
double? MtyVolume = 1;
double? MtyPrice = 1;
double? Leverage = 1;
string type = "FU";
IQueryable<CpTrade> cptradefromDb = from cptrade in db.CpTrades
where cptrade.valid == 1 && cptrade.BrokerId == "RJO" &&
cptrade.ReportDate >= reportdate.Date &&
cptrade.ReportDate < (nextdate.Date) &&
cptrade.BOTradeNumber == null
select cptrade;
IQueryable<Contract> contractrow =
from ct in db.Contracts
where ct.valid == 1
select ct;
Dictionary<string, Contract> contractdetails = contractrow.ToDictionary(k => k.id, k => k);
foreach (CpTrade cpTrade in cptradefromDb)
{
var valuedate = (DateTime) cpTrade.ValueDate;
if (cpTrade.BOSymbol == null)
{
//cpTrade.BOSymbol = GetSymbolLek(symbolmap, cpTrade.Symbol, ref MtyVolume, contractdetails,ref MtyPrice, ref valuedate, ref Leverage);
cpTrade.BOSymbol = GetSymbolRJO(symbolmap, cpTrade.Symbol, ref MtyVolume, contractdetails,
ref MtyPrice, ref valuedate, ref Leverage, ref type);
cpTrade.Price = cpTrade.Price*MtyPrice;
cpTrade.Qty = cpTrade.Qty*MtyVolume;
cpTrade.Type = type;
// cpTrade.value = cpTrade.value*Leverage;
cpTrade.ValueDate = valuedate;
}
}
SaveDBChanges(ref db);
}
RecProcess(reportdate, "RJO");
}
private void RecProcess(DateTime reportdate, string ccp)
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart + ": " + "start " + ccp + " reconciliation");
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, Map> symbolmap = getMap(ccp);
DateTime nextdate = reportdate.AddDays(1);
IQueryable<CpTrade> cptradefromDb = from cptrade in db.CpTrades
where
cptrade.valid == 1 && cptrade.BrokerId == ccp &&
cptrade.ReportDate >= reportdate.Date &&
cptrade.ReportDate < (nextdate.Date) &&
cptrade.BOTradeNumber == null
select cptrade;
if (ccp == "ABN")
cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "01"); //.Contains(o.StatusCode))
if (ccp == "Mac")
cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "A");
if (ccp == "CFH")
cptradefromDb = cptradefromDb.Where(o => o.TypeOfTrade == "OnlineTrade");
//var filteredOrders = orders.Order.Where(o => allowedStatus.Contains(o.StatusCode));
List<CpTrade> cptradelist = cptradefromDb.ToList();
foreach (CpTrade cpTrade in cptradelist)
{
if (cpTrade.BOSymbol == null)
{
Map symbolvalue;
string key = cpTrade.Symbol + cpTrade.Type;
if (cpTrade.Type == "FU")
{
if (cpTrade.ValueDate != null) key = key + cpTrade.ValueDate.Value.ToShortDateString();
}
if (symbolmap.TryGetValue(key, out symbolvalue))
{
cpTrade.BOSymbol = symbolvalue.BOSymbol;
cpTrade.Qty = cpTrade.Qty*symbolvalue.MtyVolume;
cpTrade.Price = cpTrade.Price*symbolvalue.MtyPrice;
}
db.CpTrades.Attach(cpTrade);
db.Entry(cpTrade).State = (EntityState)System.Data.Entity.EntityState.Modified;
}
}
SaveDBChanges(ref db);
db.Dispose();
DateTime TimeStartReconciliation = DateTime.Now;
AbnRecon(reportdate, cptradelist, ccp);
DateTime TimeEndReconciliation = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEndReconciliation.ToLongTimeString() + ": " +
"Reconciliation completed. Time:" +
(TimeStartReconciliation - TimeEndReconciliation).ToString() + "s");
}
private void postTradesforDate(BOaccount acc, DateTime reportdate, bool sendFee, bool sendPL, string token,
string conStr, string account, string Broker)
{
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = reportdate.AddDays(1);
IQueryable<CpTrade> cptradefromDb = from Cptrade in db.CpTrades
where Cptrade.valid == 1 && Cptrade.BrokerId == Broker &&
Cptrade.ReportDate >= reportdate.Date &&
Cptrade.ReportDate < (nextdate.Date)
//&& Cptrade.ReconAccount == null
select Cptrade;
List<CpTrade> cptradeitem = cptradefromDb.ToList();
int tradesqty = 0;
foreach (CpTrade cpTrade in cptradeitem)
{
if (cpTrade.ReconAccount == null)
{
tradesqty = BoReconPostTrade(cpTrade, acc, conStr, token, tradesqty);
if (sendFee)
{
BoReconPostFee(cpTrade, conStr, acc, token);
}
}
}
//json = FeeJsonfromCpTrade(cpTrade, accountnumber, "60002000000 - Exante Trading Account");
if (sendPL)
{
IQueryable<FT> FTfromDb = from ft in db.FT
where ft.valid == 1 && ft.brocker == acc.DBcpName &&
ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date) &&
ft.account_id == acc.BOaccountId && ft.Type == "PL"
select ft;
List<FT> FTfromDbeitem = FTfromDb.ToList();
foreach (FT ft in FTfromDbeitem)
{
BoReconPostPnL(ft, conStr, acc, token);
}
}
if (tradesqty > 0)
{
db.SaveChanges();
db.Dispose();
LogTextBox.AppendText("\r\n Uploaded trades for " + reportdate.ToShortDateString() + ": " +
tradesqty.ToString() + "/" + cptradeitem.Count);
}
}
private static void GetPortfolioOSL(Workbook ObjWorkBook, EXANTE_Entities db, DateTime reportdate,
dynamic account,
dynamic ccy)
{
Range xlRange;
Worksheet ObjWorkSheet;
// ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ObjWorkBook.Sheets["Securities"];
ObjWorkSheet =
ObjWorkBook.Worksheets.Cast<Worksheet>().FirstOrDefault(worksheet => worksheet.Name == "Securities");
if (ObjWorkSheet != null)
{
xlRange = ObjWorkSheet.UsedRange;
int add = 0;
var curr = (string) xlRange.Cells[2, 5].value2;
if (curr.IndexOf("Place of keeping") > -1) add = 1;
//Open balance
int i = 4;
while ((xlRange.Cells[i, 6 + add].value2 != null) & ((xlRange.Cells[i, 6 + add].value2 != "")))
{
db.RowBalance.Add(new RowBalance
{
ccy = xlRange.Cells[i, 6 + add].value2,
cp = "OPEN",
Type = "Securities",
Value = xlRange.Cells[i, 18 + add].value2,
Timestamp = DateTime.UtcNow,
ReportDate = reportdate,
Exchange = xlRange.Cells[i, 5 + add].value2,
Comment = "Qty:" + xlRange.Cells[i, 17 + add].value2,
account = account
});
i++;
}
db.RowBalance.Add(new RowBalance
{
ccy = ccy,
cp = "OPEN",
Type = "TotalSecurities",
Value = Convert.ToDouble(xlRange.Cells[i, 19 + add].value2),
Timestamp = DateTime.UtcNow,
ReportDate = reportdate,
Comment = "Planned portfolio value",
account = account
});
}
}
private List<InitialTrade> ParseBrockerExcelToCpTrade(string filename, ColumnMapping cMapping, int startline = 0)
{
var ObjExcel = new Application();
//Открываем книгу.
Workbook ObjWorkBook = ObjExcel.Workbooks.Open(filename, 0, false, 5, "", "",
false,
XlPlatform.xlWindows,
"",
true, false, 0, true,
false, false);
//Выбираетам таблицу(лист).
Worksheet ObjWorkSheet;
if (cMapping.cTabName != null)
{
ObjWorkSheet =
ObjWorkBook.Worksheets.Cast<Worksheet>()
.FirstOrDefault(worksheet => worksheet.Name == cMapping.cTabName);
}
else
{
ObjWorkSheet = ObjWorkBook.Worksheets[1];
// .Cast<Worksheet>().FirstOrDefault(worksheet => worksheet.Name == cMapping.cTabName)
}
if (ObjWorkSheet != null)
{
// ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) ObjWorkBook.Sheets[cMapping.cTabName];
Range xlRange = ObjWorkSheet.UsedRange;
var tradescounter = new Dictionary<DateTime, int>();
int i = startline;
if (startline == 0) i = (int) cMapping.cLineStart;
var lInitTrades = new List<InitialTrade>();
int n = xlRange.Rows.Count;
int add = 0;
if (i != 1)
{
var curr = (string) xlRange.Cells[i - 1, 12].value2;
if ((curr != null) && (curr.IndexOf("Place of Settlement") > -1)) add = 1;
}
while (i <= n)
{
if (xlRange.Cells[i, cMapping.cTradeDate].value2 != null)
{
DateTime tradeDate = getDate(cMapping.DateFormat, xlRange.Cells[i, cMapping.cTradeDate].value2);
dynamic reportdate = cMapping.cReportDate != null
? getDate(cMapping.ReportDateFormat,
xlRange.Cells[i, cMapping.cReportDate].value2)
: tradeDate.Date;
dynamic valueDate = cMapping.cValuedate != null
? getDate(cMapping.ValueDateFormat,
xlRange.Cells[i, cMapping.cValuedate].value2)
: null;
if (cMapping.cTradeTime != null)
{
string crtFormat = "HH:mm:ss";
dynamic crtValue = xlRange.Cells[i, cMapping.cTradeTime].value2;
if (cMapping.TimeFormat != null)
{
crtFormat = cMapping.TimeFormat;
}
if (crtFormat.Length == 6)
{
dynamic diffdigit = crtFormat.Length - crtValue.ToString().Length;
if (diffdigit > 0) crtValue = "0" + crtValue;
}
dynamic time = DateFromExcelCell(crtValue, crtFormat);
// : DateFromExcelCell(xlRange.Cells[i, cMapping.cTradeTime].value2, "HH:mm:ss");
var ts = new TimeSpan(time.Hour, time.Minute, time.Second);
tradeDate = tradeDate.Date + ts;
}
double qty;
if (cMapping.cQtySell == null)
{
qty = xlRange.Cells[i, cMapping.cQty].value2;
if (cMapping.cSide != null)
{
dynamic side = xlRange.Cells[i, cMapping.cSide].value2;
if (side != null)
{
side = side.ToUpper();
if ((side == "SELL") || (side == "S") || (side.Contains("ПРОДАЖА")))
qty = -Math.Abs(qty);
}
}
}
else
{
double qtybuy = 0;
if (xlRange.Cells[i, cMapping.cQty].value2 != null)
qtybuy = xlRange.Cells[i, cMapping.cQty].value2;
double qtysell = 0;
if (xlRange.Cells[i, cMapping.cQtySell].value2 != null)
qtysell = xlRange.Cells[i, cMapping.cQtySell].value2;
qty = qtybuy - qtysell;
}
dynamic ReportDate = reportdate;
DateTime TradeDate = tradeDate;
dynamic BrokerId =
cMapping.cBrokerId != null
? xlRange.Cells[i, cMapping.cBrokerId].value2
: cMapping.Brocker;
dynamic Symbol = Convert.ToString(xlRange.Cells[i, cMapping.cSymbol].value2);
dynamic Type = cMapping.cType != null ? xlRange.Cells[i, cMapping.cType].value2 : cMapping.Type;
double Qty = qty;
dynamic Price = Math.Round(xlRange.Cells[i, cMapping.cPrice + add].value2, 10);
dynamic ValueDate = valueDate;
dynamic ExchangeFees =
cMapping.cExchangeFees != null
? xlRange.Cells[i, cMapping.cExchangeFees + add].value2
: null;
dynamic Fee = cMapping.cFee != null ? xlRange.Cells[i, cMapping.cFee + add].value2 : null;
dynamic Fee2 = cMapping.cFee2 != null ? xlRange.Cells[i, cMapping.cFee2 + add].value2 : null;
dynamic Fee3 = cMapping.cFee3 != null ? xlRange.Cells[i, cMapping.cFee3 + add].value2 : null;
dynamic value = cMapping.cValue != null ? xlRange.Cells[i, cMapping.cValue + add].value2 : null;
DateTime Timestamp = DateTime.UtcNow;
dynamic exchangeOrderId =
cMapping.cExchangeOrderId != null
? Convert.ToString(xlRange.Cells[i, cMapping.cExchangeOrderId].value2)
: null;
dynamic ClearingFeeCcy =
cMapping.cClearingFeeCcy != null
? xlRange.Cells[i, cMapping.cClearingFeeCcy + add].value2
: null;
dynamic ccy = cMapping.cCcy != null ? xlRange.Cells[i, cMapping.cCcy + add].value2 : null;
dynamic ExchFeeCcy =
cMapping.cExchFeeCcy != null
? xlRange.Cells[i, cMapping.cExchFeeCcy + add].value2
: null;
dynamic TypeOfTrade =
cMapping.cTypeOfTrade != null
? xlRange.Cells[i, cMapping.cTypeOfTrade].value2
: null;
dynamic Comment = cMapping.cComment != null ? xlRange.Cells[i, cMapping.cComment].value2 : null;
dynamic Strike = cMapping.cStrike != null ? xlRange.Cells[i, cMapping.cStrike].value2 : null;
dynamic AccruedInterest =
cMapping.cInterest != null ? xlRange.Cells[i, cMapping.cInterest].value2 : null;
dynamic Account =
cMapping.cAccount != null ? xlRange.Cells[i, cMapping.cAccount + add].value2 : null;
dynamic TradeId =
cMapping.cTradeId != null
? Convert.ToString(xlRange.Cells[i, cMapping.cTradeId + add].value2)
: null;
lInitTrades.Add(new InitialTrade
{
ReportDate = reportdate,
TradeDate = tradeDate,
BrokerId =
cMapping.cBrokerId != null
? xlRange.Cells[i, cMapping.cBrokerId].value2
: cMapping.Brocker,
Symbol = Convert.ToString(xlRange.Cells[i, cMapping.cSymbol].value2),
Type = cMapping.cType != null ? xlRange.Cells[i, cMapping.cType].value2 : cMapping.Type,
Qty = qty,
Price = Math.Round(xlRange.Cells[i, cMapping.cPrice + add].value2, 10),
ValueDate = valueDate,
ExchangeFees =
cMapping.cExchangeFees != null
? xlRange.Cells[i, cMapping.cExchangeFees + add].value2
: null,
Fee = cMapping.cFee != null ? xlRange.Cells[i, cMapping.cFee + add].value2 : null,
Fee2 = cMapping.cFee2 != null ? xlRange.Cells[i, cMapping.cFee2 + add].value2 : null,
Fee3 = cMapping.cFee3 != null ? xlRange.Cells[i, cMapping.cFee3 + add].value2 : null,
value = cMapping.cValue != null ? xlRange.Cells[i, cMapping.cValue + add].value2 : null,
Timestamp = DateTime.UtcNow,
exchangeOrderId =
cMapping.cExchangeOrderId != null
? Convert.ToString(xlRange.Cells[i, cMapping.cExchangeOrderId].value2)
: null,
ClearingFeeCcy =
cMapping.cClearingFeeCcy != null
? xlRange.Cells[i, cMapping.cClearingFeeCcy + add].value2
: null,
ccy = cMapping.cCcy != null ? xlRange.Cells[i, cMapping.cCcy + add].value2 : null,
ExchFeeCcy =
cMapping.cExchFeeCcy != null
? xlRange.Cells[i, cMapping.cExchFeeCcy + add].value2
: null,
TypeOfTrade =
cMapping.cTypeOfTrade != null
? xlRange.Cells[i, cMapping.cTypeOfTrade].value2
: null,
Comment = cMapping.cComment != null ? xlRange.Cells[i, cMapping.cComment].value2 : null,
Strike = cMapping.cStrike != null ? xlRange.Cells[i, cMapping.cStrike].value2 : null,
AccruedInterest =
cMapping.cInterest != null ? xlRange.Cells[i, cMapping.cInterest].value2 : null,
Account =
cMapping.cAccount != null ? xlRange.Cells[i, cMapping.cAccount + add].value2 : null,
TradeId =
cMapping.cTradeId != null
? Convert.ToString(xlRange.Cells[i, cMapping.cTradeId + add].value2)
: null
});
if (tradescounter.ContainsKey(reportdate))
{
tradescounter[reportdate] = tradescounter[reportdate] + 1;
}
else
{
tradescounter.Add(reportdate, 1);
}
}
i++;
}
var db = new EXANTE_Entities(_currentConnection);
foreach (InitialTrade initialTrade in lInitTrades)
{
db.InitialTrades.Add(initialTrade);
}
db.SaveChanges();
db.Dispose();
ObjWorkBook.Close();
ObjExcel.Quit();
Marshal.FinalReleaseComObject(ObjWorkBook);
Marshal.FinalReleaseComObject(ObjExcel);
LogTextBox.AppendText("\r\nTrades uploaded:");
foreach (var pair in tradescounter)
{
LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value);
}
//reportdate = tradescounter.FirstOrDefault().Key
return lInitTrades;
}
else
{
ObjExcel.Quit();
Marshal.FinalReleaseComObject(ObjWorkBook);
Marshal.FinalReleaseComObject(ObjExcel);
return null;
}
}
private void ParseBrockerCsvToEmir(string filename, Dictionary<string, Emir_Mapping> cMapping)
{
var tradescounter = new Dictionary<DateTime, int>();
var lInitTrades = new List<Emir>();
var db = new EXANTE_Entities(_currentConnection);
IQueryable<counterparty> cpfromDb = from cp in db.counterparties
select cp;
Dictionary<string, int> cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id);
var reader = new StreamReader(openFileDialog2.FileName);
string lineFromFile;
IQueryable<Contract> contractrow =
from ct in db.Contracts
where ct.valid == 1
select ct;
int i = 1;
Emir_Mapping parameters = cMapping.First().Value;
while ((i < parameters.cLineStart) && (!reader.EndOfStream))
{
lineFromFile = reader.ReadLine();
i++;
}
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
string[] rowstring = lineFromFile.Split(Convert.ToChar(parameters.Delimeter));
DateTime cpValueDate;
if (rowstring[6].Length == 4)
{
cpValueDate = DateTime.ParseExact(rowstring[6], "yyMM", CultureInfo.CurrentCulture);
}
else
{
cpValueDate = DateTime.ParseExact(rowstring[6], "yyyyMMdd", CultureInfo.CurrentCulture);
}
string map_id = rowstring[5];
if (rowstring[7] == "O")
{
map_id = map_id + "OP";
}
map_id = map_id + cpValueDate.ToShortDateString();
Emir_Mapping map = cMapping[map_id];
var timedifference = new TimeSpan((int) map.TimeDifference, 0, 0);
string Buy___Sell_Indicator = rowstring[parameters.cBuySell];
string Instrument_ID_Taxonomy = map.InstrumentIDTaxonomy;
string Instrument_ID = map.InstrumentID;
string Instrument_Classification = map.InstrumentClassification;
string Underlying_Instrument_ID = map.InstrumentType;
string Notional_Currency_1 = map.NotionalCurrency1;
string Deliverable_Currency = map.DeliverableCurrency;
string UTI = rowstring[24] + rowstring[25];
string MiFID_Transaction_Reference_Number = rowstring[28];
string Venue_ID = map.VenueId;
double? Price___Rate = (Convert.ToDouble(rowstring[13]) + Convert.ToDouble(rowstring[12]))*
map.CpMtyPrice;
string Price_Notation = map.PriceNotation;
string Price_Multiplier = map.PriceMultiplier.ToString();
string Notional =
(map.CpMtyPrice*map.PriceMultiplier*Convert.ToDouble(rowstring[11])*
(Convert.ToDouble(rowstring[12]) + Convert.ToDouble(rowstring[13]))).ToString();
string Quantity = rowstring[11];
string Delivery_Type = map.DeliveryType;
DateTime Execution_Timestamp = Convert.ToDateTime(rowstring[27]) - timedifference;
DateTime Effective_Date = Convert.ToDateTime(rowstring[0]);
DateTime? Maturity_Date = map.MaturityDate;
DateTime Confirmation_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference;
DateTime Clearing_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference;
string CCP_ID = parameters.cp;
string Floating_Rate_Payment_Frequency = map.FloatingRatePaymentFrequency;
string Floating_Rate_Reset_Frequency = map.FloatingRateResetFrequency;
string Floating_Rate_Leg_2 = map.FloatingRateLeg2;
string Currency_2 = map.Currency2;
string Exchange_Rate_Basis = map.ExchangeRateBasis;
string Commodity_Base = map.CommodityBase;
string Commodity_Details = map.CommodityDetails;
string Put_Call = null;
string Option_Exercise_Type = null;
string Strike_Price = null;
string ForwardExchangeRate = null;
if (map.ForwardExchangeRateMty != null)
{
ForwardExchangeRate = (map.ForwardExchangeRateMty*Price___Rate).ToString();
}
if (map.cPutCall != null)
{
Put_Call = rowstring[(int) map.cPutCall];
// Option_Exercise_Type =map.
Strike_Price = Convert.ToDouble(rowstring[(int) map.cStrikePrice]).ToString();
ForwardExchangeRate =
(Convert.ToDouble(rowstring[(int) map.cStrikePrice])*map.ForwardExchangeRateMty).ToString();
}
lInitTrades.Add(new Emir
{
ReportDate = Effective_Date,
cp = map.Brocker,
Timestamp = DateTime.Now,
Common_Data_Delegated = "N",
Reporting_Firm_ID = "635400MMGYK7HLRQGV31",
Other_Counterparty_ID = parameters.cp,
Other_Counterparty_ID_Type = "L",
Reporting_Firm_Country_Code_of_Branch = "MT",
Reporting_Firm_Corporate_Sector = "F",
Reporting_Firm_Financial_Status = "F",
Beneficiary_ID = "635400MMGYK7HLRQGV31",
Beneficiary_ID_Type = "L",
Trading_Capacity = "P",
Buy___Sell_Indicator = rowstring[parameters.cBuySell],
Counterparty_EEA_Status = "N",
Instrument_ID_Taxonomy = map.InstrumentIDTaxonomy,
Instrument_ID = map.InstrumentID,
Instrument_Classification = map.InstrumentClassification,
Underlying_Instrument_ID = map.UnderlyingInstrumentID,
Underlying_Instrument_ID_Type = map.UnderlyingInstrumentIDType,
Notional_Currency_1 = map.NotionalCurrency1,
Deliverable_Currency = map.DeliverableCurrency,
UTI = rowstring[24] + rowstring[25],
MiFID_Transaction_Reference_Number = rowstring[28],
Venue_ID = map.VenueId,
Compression_Exercise = "N",
Price___Rate = Price___Rate.ToString(),
Price_Notation = map.PriceNotation,
Price_Multiplier = map.PriceMultiplier.ToString(),
Notional = (map.PriceMultiplier*Convert.ToDouble(rowstring[11])*Price___Rate).ToString(),
Quantity = Convert.ToDouble(rowstring[11]).ToString(),
Delivery_Type = map.DeliveryType,
Execution_Timestamp = Convert.ToDateTime(rowstring[27]) - timedifference,
Effective_Date = Convert.ToDateTime(rowstring[0]),
Maturity_Date = map.MaturityDate,
Confirmation_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference,
Confirmation_Type = "E",
Clearing_Obligation = "Y",
Cleared = "Y",
Clearing_Timestamp = Convert.ToDateTime(rowstring[26]) - timedifference,
CCP_ID = parameters.cp,
CCP_ID_Type = "L",
Intragroup = "N",
Floating_Rate_Payment_Frequency = map.FloatingRatePaymentFrequency,
Floating_Rate_Reset_Frequency = map.FloatingRateResetFrequency,
Floating_Rate_Leg_2 = map.FloatingRateLeg2,
Currency_2 = map.Currency2,
Forward_Exchange_Rate = ForwardExchangeRate,
Exchange_Rate_Basis = map.ExchangeRateBasis,
Commodity_Base = map.CommodityBase,
Commodity_Details = map.CommodityDetails,
Put___Call = Put_Call,
Option_Exercise_Type = map.OptionExerciseType,
Strike_Price = Strike_Price,
Action_Type = "N",
Message_Type = "T",
Instrument_Description = map.InstrumentDescription,
Fixed_Rate_Leg_1 = map.FixedRateLeg1.ToString(),
Fixed_Rate_Day_Count = map.FixedRateDayCount,
Fixed_Leg_Payment_Frequency = map.FixedLegPaymentFrequency
});
if (tradescounter.ContainsKey(Effective_Date))
{
tradescounter[Effective_Date] = tradescounter[Effective_Date] + 1;
}
else
{
tradescounter.Add(Effective_Date, 1);
}
}
foreach (Emir emir in lInitTrades)
{
db.Emir.Add(emir);
}
db.SaveChanges();
db.Dispose();
LogTextBox.AppendText("\r\nTrades uploaded:");
foreach (var pair in tradescounter)
{
LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value);
}
}
private List<InitialTrade> ParseBrockerCsvToCpTrade(string filename, ColumnMapping cMapping)
{
var tradescounter = new Dictionary<DateTime, int>();
var lInitTrades = new List<InitialTrade>();
var db = new EXANTE_Entities(_currentConnection);
IQueryable<counterparty> cpfromDb = from cp in db.counterparties
select cp;
Dictionary<string, int> cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id);
var reader = new StreamReader(openFileDialog2.FileName);
string lineFromFile;
IQueryable<Contract> contractrow =
from ct in db.Contracts
where ct.valid == 1
select ct;
// var contractdetails = contractrow.ToDictionary(k => k.id, k => k);
int i = 1;
while ((i < cMapping.cLineStart) && (!reader.EndOfStream))
{
lineFromFile = reader.ReadLine();
i++;
}
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
if (cMapping.Replacesymbols == "ST")
{
lineFromFile = lineFromFile.Replace("\"", "");
}
else
{
lineFromFile = lineFromFile.Replace(cMapping.Replacesymbols, "");
}
string[] rowstring = lineFromFile.Split(Convert.ToChar(cMapping.Delimeter));
DateTime tradeDate = cMapping.cTradeDate != null
? DateTime.ParseExact(rowstring[(int) cMapping.cTradeDate], cMapping.DateFormat,
CultureInfo.CurrentCulture)
: new DateTime(2011, 01, 01);
DateTime reportdate = cMapping.cReportDate != null
? DateTime.ParseExact(rowstring[(int) cMapping.cReportDate],
cMapping.ReportDateFormat, CultureInfo.CurrentCulture)
: tradeDate;
// var reportdate = DateTime.ParseExact(rowstring[(int)cMapping.cReportDate], cMapping.DateFormat, CultureInfo.CurrentCulture);
if (cMapping.cTradeTime != null)
{
DateTime time = DateTime.ParseExact(rowstring[(int) cMapping.cTradeTime], "HH:mm:ss",
CultureInfo.CurrentCulture);
var ts = new TimeSpan(time.Hour, time.Minute, time.Second);
tradeDate = tradeDate.Date + ts;
}
double qty;
if (cMapping.cQtySell == null)
{
qty = Convert.ToDouble(rowstring[(int) cMapping.cQty]);
}
else
{
qty = Convert.ToDouble(rowstring[(int) cMapping.cQty]) -
Convert.ToDouble(rowstring[(int) cMapping.cQtySell]);
}
if (cMapping.cSide != null)
{
if (rowstring[(int) cMapping.cSide] == "2") qty = -qty;
if (rowstring[(int) cMapping.cSide].ToUpper() == "SELL") qty = -qty;
if (rowstring[(int) cMapping.cSide].ToUpper() == "SLD") qty = -qty;
if (rowstring[(int) cMapping.cSide].ToUpper() == "S") qty = -qty;
}
string symbol_id = rowstring[(int) cMapping.cSymbol].TrimEnd();
double price = 0;
if (cMapping.cPriceSell == null)
{
price =
Math.Round(double.Parse(rowstring[(int) cMapping.cPrice], CultureInfo.InvariantCulture), 7);
}
else
{
if (qty < 0)
{
price =
Math.Round(
double.Parse(rowstring[(int) cMapping.cPriceSell], CultureInfo.InvariantCulture), 7);
}
else
{
price = Math.Round(
double.Parse(rowstring[(int) cMapping.cPrice], CultureInfo.InvariantCulture), 7);
}
}
double? Fee;
if (cMapping.cFee != null)
{
Fee = double.Parse(rowstring[(int) cMapping.cFee], CultureInfo.InvariantCulture);
if (cMapping.cClearingFee != null)
{
Fee =
Math.Round(
(double)
(Fee +
double.Parse(rowstring[(int) cMapping.cClearingFee], CultureInfo.InvariantCulture)), 2);
}
}
else
{
if (cMapping.cClearingFee != null)
{
Fee =
Math.Round(
double.Parse(rowstring[(int) cMapping.cClearingFee], CultureInfo.InvariantCulture), 2);
}
else Fee = null;
}
double? value;
if (cMapping.cValue != null)
{
value = Math.Abs(double.Parse(rowstring[(int) cMapping.cValue], CultureInfo.InvariantCulture));
if (qty > 0) value = -value;
}
else
{
value = -price*qty;
if (cMapping.Mty != null)
{
value = value*double.Parse(rowstring[(int) cMapping.Mty], CultureInfo.InvariantCulture);
}
value = Math.Round((double) value, 2);
}
//? double.Parse(rowstring[(int)cMapping.cValue], CultureInfo.InvariantCulture) * double.Parse(rowstring[(int)cMapping.Mty], CultureInfo.InvariantCulture)
//: null;
// var cp_id = getCPid(rowstring[idcp].Trim(), cpdic);
/* if (symbol_id.Contains("PUT") || symbol_id.Contains("CALL"))
{
typeofInstrument = "OP";
}*/
DateTime ReportDate = reportdate;
DateTime TradeDate = tradeDate;
string BrokerId = cMapping.cBrokerId != null ? rowstring[(int) cMapping.cBrokerId] : cMapping.Brocker;
string Symbol = symbol_id;
double Qty = qty;
double Price = price;
DateTime? ValueDate = cMapping.cValuedate != null
? DateTime.ParseExact(rowstring[(int) cMapping.cValuedate],
cMapping.ValueDateFormat,
CultureInfo.CurrentCulture)
: (DateTime?) null;
double? ExchangeFees =
cMapping.cExchangeFees != null
? double.Parse(rowstring[(int) cMapping.cExchangeFees], CultureInfo.InvariantCulture)
: (double?) null;
double? Fee22 = Fee;
string TypeOfTrade = cMapping.cTypeOfTrade != null ? rowstring[(int) cMapping.cTypeOfTrade] : null;
string Type = cMapping.cType != null ? rowstring[(int) cMapping.cType] : cMapping.Type;
double? value2 = value;
DateTime Timestamp = DateTime.UtcNow;
string exchangeOrderId =
cMapping.cExchangeOrderId != null
? Convert.ToString(rowstring[(int) cMapping.cExchangeOrderId])
: null;
string Comment = cMapping.cComment != null ? rowstring[(int) cMapping.cComment] : null;
string ExchFeeCcy =
cMapping.cExchFeeCcy != null ? rowstring[(int) cMapping.cExchFeeCcy].TrimEnd() : null;
string ClearingFeeCcy =
cMapping.cClearingFeeCcy != null
? rowstring[(int) cMapping.cClearingFeeCcy].TrimEnd()
: null;
string ccy = cMapping.cCcy != null ? rowstring[(int) cMapping.cCcy].TrimEnd() : null;
double? Strike =
cMapping.cStrike != null
? double.Parse(rowstring[(int) cMapping.cStrike], CultureInfo.InvariantCulture)
: (double?) null;
string OptionType =
cMapping.cOptionType != null ? rowstring[(int) cMapping.cOptionType].TrimEnd() : null;
double? Fee2 =
cMapping.cFee2 != null
? double.Parse(rowstring[(int) cMapping.cFee2], CultureInfo.InvariantCulture)
: (double?) null;
double? Fee3 =
cMapping.cFee3 != null
? double.Parse(rowstring[(int) cMapping.cFee3], CultureInfo.InvariantCulture)
: (double?) null;
string test = cMapping.cAccount != null
? rowstring[(int) cMapping.cAccount]
: null;
lInitTrades.Add(new InitialTrade
{
ReportDate = reportdate,
TradeDate = tradeDate,
BrokerId = cMapping.cBrokerId != null ? rowstring[(int) cMapping.cBrokerId] : cMapping.Brocker,
Symbol = symbol_id,
Qty = qty,
Price = price,
ValueDate = cMapping.cValuedate != null
? DateTime.ParseExact(rowstring[(int) cMapping.cValuedate],
cMapping.ValueDateFormat,
CultureInfo.CurrentCulture)
: (DateTime?) null,
ExchangeFees =
cMapping.cExchangeFees != null
? double.Parse(rowstring[(int) cMapping.cExchangeFees], CultureInfo.InvariantCulture)
: (double?) null,
Fee = Fee,
TypeOfTrade = cMapping.cTypeOfTrade != null ? rowstring[(int) cMapping.cTypeOfTrade] : null,
Type = cMapping.cType != null ? rowstring[(int) cMapping.cType] : cMapping.Type,
value = value,
Timestamp = DateTime.UtcNow,
exchangeOrderId =
cMapping.cExchangeOrderId != null
? Convert.ToString(rowstring[(int) cMapping.cExchangeOrderId])
: null,
Comment = cMapping.cComment != null ? rowstring[(int) cMapping.cComment] : null,
ExchFeeCcy =
cMapping.cExchFeeCcy != null ? rowstring[(int) cMapping.cExchFeeCcy].TrimEnd() : null,
ClearingFeeCcy =
cMapping.cClearingFeeCcy != null
? rowstring[(int) cMapping.cClearingFeeCcy].TrimEnd()
: null,
ccy = cMapping.cCcy != null ? rowstring[(int) cMapping.cCcy].TrimEnd() : null,
Strike =
cMapping.cStrike != null
? double.Parse(rowstring[(int) cMapping.cStrike], CultureInfo.InvariantCulture)
: (double?) null,
OptionType =
cMapping.cOptionType != null ? rowstring[(int) cMapping.cOptionType].TrimEnd() : null,
Fee2 =
cMapping.cFee2 != null
? double.Parse(rowstring[(int) cMapping.cFee2], CultureInfo.InvariantCulture)
: (double?) null,
Fee3 =
cMapping.cFee3 != null
? double.Parse(rowstring[(int) cMapping.cFee3], CultureInfo.InvariantCulture)
: (double?) null,
Account =
cMapping.cAccount != null
? rowstring[(int) cMapping.cAccount]
: null,
TradeId =
cMapping.cTradeId != null ? rowstring[(int) cMapping.cTradeId] : null
});
if (tradescounter.ContainsKey(reportdate))
{
tradescounter[reportdate] = tradescounter[reportdate] + 1;
}
else
{
tradescounter.Add(reportdate, 1);
}
i++;
}
foreach (InitialTrade initialTrade in lInitTrades)
{
db.InitialTrades.Add(initialTrade);
}
db.SaveChanges();
db.Dispose();
LogTextBox.AppendText("\r\nTrades uploaded:");
foreach (var pair in tradescounter)
{
LogTextBox.AppendText("\r\n" + pair.Key.ToShortDateString() + ":" + pair.Value);
}
return lInitTrades;
}
private void updateBalance(List<string> rowlist,DateTime reportdate)
{
var dbentity = new EXANTE_Entities();
var cpidfromDb = from cp in dbentity.DailyChecks
where cp.Table == "Daily" && cp.date== reportdate
select cp.status;
var listforDb = new List<ABN_cashposition>();
foreach (var row in rowlist)
{
var value = row.Substring(90, 18);
value = value.Substring(0, value.Count() - 2) + "."+value.Substring(value.Count() - 2, 2);
dbentity.ABN_cashposition.Add(new ABN_cashposition
{
ReportDate =reportdate,
Currency= row.Substring(54, 3),
Value = row[108] != 'C'
? -1*double.Parse(value, CultureInfo.InvariantCulture)
: double.Parse(value, CultureInfo.InvariantCulture),
valid=1,
User = "******",
TimeStamp =DateTime.Now,
Description = row.Substring(109, 40).Trim()
});
}
dbentity.SaveChanges();
/* dbentity.DailyChecks.Add(new DailyCheck
{
cp_id = null,
date = reportdate,
status = "ok",
user = "******",
valid = true,
timestamp =DateTime.Now,
Table = "ABN_cashposition"
});
dbentity.SaveChanges();*/
}
private int? getCPid(string cpname,Dictionary<string,int> cpdic)
{
if (cpname != null)
{
int cp_id;
if (cpdic.TryGetValue(cpname, out cp_id))
{
return cp_id;
}
else
{
var dbentity = new EXANTE_Entities();
dbentity.counterparties.Add(new counterparty
{Name =cpname});
dbentity.SaveChanges();
var cpidfromDb = from cp in dbentity.counterparties
where cp.Name == cpname
select cp.cp_id;
cpdic.Add(cpname,cpidfromDb.First());
return cpidfromDb.First();
}
}
else
{
Log("Нет идентификатора counterparty");
return 0;
}
}
private List<InitialTrade> TradeParsing(string brocker, string filetype, string mappingtype, string identify)
{
DialogResult result = openFileDialog2.ShowDialog();
var lInitTrades = new List<InitialTrade>();
if (result == DialogResult.OK) // Test result.
{
// var symbolmap = getMapping("RJO");
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, ColumnMapping> cMapping = (from ct in db.ColumnMappings
where
ct.Brocker == brocker && ct.FileType == filetype &&
ct.Account == identify
// "CSV"
select ct).ToDictionary(k => k.Type, k => k);
if (filetype == "CSV")
{
lInitTrades.AddRange(ParseBrockerCsvToCpTrade(openFileDialog2.FileName, cMapping[mappingtype]));
}
else
{
lInitTrades.AddRange(ParseBrockerExcelToCpTrade(openFileDialog2.FileName, cMapping[mappingtype]));
}
return lInitTrades;
}
else return lInitTrades;
}
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 TradesParser_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
var reportdate = new DateTime(2011, 01, 01);
var db = new EXANTE_Entities(_currentConnection);
var reader = new StreamReader(openFileDialog2.FileName);
var allfromFile = new List<Ctrade>();
const int GMToffset = 4; //gmt offset from BO
const int nextdaystarthour = 20; //start new day for FORTS
const string template = "FORTS";
DateTime nextdayvalueform = Fortsnextday.Value;
string lineFromFile = reader.ReadLine();
TradesParserStatus.Text = "Processing";
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText(TimeStart.ToLongTimeString() + ": " + "start BO trades uploading");
int index = 1;
bool checkMalta = checkBoxMalta.Checked;
if (lineFromFile != null)
{
string[] rowstring = lineFromFile.Split(Delimiter);
int idDate = -1,
idSymbol = -1,
idAccount = -1,
idqty = -1,
idprice = -1,
idside = -1,
idfees = -1,
iduser = -1,
idcurrency = -1,
idorderid = -1,
idbrokerTimeDelta = -1,
idexchangeOrderId = -1,
idcontractMultiplier = -1,
idtradeNumber = -1,
idcounterparty = -1,
idgateway = -1,
idtradeType = -1,
idSettlementCp = -1,
idtradedVolume = -1,
idcptime = -1,
idorderPos = -1,
idvalueDate = -1;
for (int i = 0; i < rowstring.Length; i++)
{
switch (rowstring[i])
{
case "gwTime":
idDate = i;
break;
case "counterpartyTime":
idcptime = i;
break;
case "symbolId":
idSymbol = i;
break;
case "accountId":
idAccount = i;
break;
case "quantity":
idqty = i;
break;
case "price":
idprice = i;
break;
case "side":
idside = i;
break;
case "commission":
idfees = i;
break;
case "userId":
iduser = i;
break;
case "currency":
idcurrency = i;
break;
case "tradeType":
idtradeType = i;
break;
case "orderId":
idorderid = i;
break;
case "brokerTimeDelta":
idbrokerTimeDelta = i;
break;
case "orderPos":
idorderPos = i;
break;
case "exchangeOrderId":
idexchangeOrderId = i;
break;
case "contractMultiplier":
idcontractMultiplier = i;
break;
case "executionCounterparty":
idcounterparty = i;
break;
case "gatewayId":
idgateway = i;
break;
case "valueDate":
idvalueDate = i;
break;
case "settlementCounterparty":
idSettlementCp = i;
break;
case "tradedVolume":
idtradedVolume = i;
break;
default:
Console.WriteLine("Additional fields in the tr.file!");
break;
}
}
string stringindex = Convert.ToString(reportdate.Year);
if (reportdate.Month < 10) stringindex = string.Concat(stringindex, "0");
stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Month));
if (reportdate.Day < 10) stringindex = string.Concat(stringindex, "0");
stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Day));
long initialindex = Convert.ToInt64(stringindex);
IQueryable<Contract> contractrow =
from ct in db.Contracts
where ct.valid == 1
select ct;
Dictionary<string, DateTime?> contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate);
string currntmonth = reportdate.Year + "-" + reportdate.Month;
Dictionary<string, long> checkId =
(from ct in db.Ctrades
where ct.BOtradeTimestamp.ToString().Contains("2016-02-12")
select ct).ToDictionary(k => (k.order_id.ToString() + k.orderPos.ToString()), k => k.fullid);
;
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
if (lineFromFile == null) continue;
rowstring = lineFromFile.Split(Delimiter);
string id = string.Concat(rowstring[idorderid], rowstring[idorderPos]);
if (!checkId.ContainsKey(id))
{
DateTime? valuedate;
if (!contractdetails.TryGetValue(rowstring[idSymbol], out valuedate))
{
valuedate = new DateTime(2011, 01, 01);
//todo fill correct value date from file
var test = new Contract
{
id = rowstring[idSymbol],
Contract1 = rowstring[idSymbol],
Exchange = "Needtoupdate",
Type = "Needtoupdate",
Leverage =
(idcontractMultiplier > (rowstring.Length - 1)) ||
(rowstring[idcontractMultiplier] == "")
? 1
: double.Parse(rowstring[idcontractMultiplier],
CultureInfo.InvariantCulture),
ValueDate = valuedate, //Convert.ToDateTime(rowstring[idvalueDate]),
Currency =
idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency],
Margin = 0,
FlatMargin = 0,
Canbesettled = true,
UpdateDate = DateTime.UtcNow,
commission =
double.Parse(rowstring[idfees], CultureInfo.InvariantCulture)/
double.Parse(rowstring[idqty], CultureInfo.InvariantCulture),
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******"
};
db.Contracts.Add(test);
SaveDBChanges(ref db);
contractrow =
from ct in db.Contracts
where ct.valid == 1
select ct;
contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate);
}
int side = 1;
if (rowstring[idside] == "sell") side = -1;
DateTime vBOtradeTimestamp = Convert.ToDateTime(rowstring[idDate]);
if (rowstring[idSymbol].IndexOf(template) > 0)
{
DateTime fortscurrentDate = Convert.ToDateTime(rowstring[idDate]);
string initialdate = fortscurrentDate.ToShortDateString();
fortscurrentDate = fortscurrentDate.AddHours(24 - nextdaystarthour + GMToffset);
if (initialdate != fortscurrentDate.ToShortDateString())
fortscurrentDate = nextdayvalueform;
rowstring[idDate] = fortscurrentDate.ToShortDateString();
}
index++;
if (index > 0)
{
/* var ExchangeOrderId = rowstring[idexchangeOrderId];
var account_id = rowstring[idAccount];
var Date = Convert.ToDateTime(rowstring[idDate]);
var symbol_id = rowstring[idSymbol];
var qty = rowstring[idqty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[idqty])*side
: double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side;
var price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture);
var cp_id = rowstring[idcounterparty];
var fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture);
var value_date = valuedate; //Convert.ToDateTime(rowstring[idvalueDate]),
var currency = idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency];
var Timestamp = DateTime.UtcNow;
var username = rowstring[iduser];
var order_id = rowstring[idorderid];
// var gatewayId = rowstring[idgateway];
var BOtradeTimestamp = vBOtradeTimestamp;
var mty = double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture);
var SettlementCp = rowstring[idSettlementCp];
var Value = double.Parse(rowstring[idtradedVolume], CultureInfo.InvariantCulture);
/* var cptimestamp = rowstring[idcptime]==""
? null
: Convert.ToDateTime(rowstring[idcptime]);*/
db.Ctrades.Add(new Ctrade
{
ExchangeOrderId = rowstring[idexchangeOrderId],
account_id = rowstring[idAccount],
Date = Convert.ToDateTime(rowstring[idDate]),
symbol_id = rowstring[idSymbol],
qty = rowstring[idqty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[idqty])*side
: double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side,
price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture),
cp_id = rowstring[idcounterparty],
fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture),
value_date = valuedate,
currency = idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency],
orderPos = Convert.ToInt32(rowstring[idorderPos]),
Timestamp = DateTime.UtcNow,
valid = 1,
username = rowstring[iduser],
order_id = rowstring[idorderid],
// gatewayId = rowstring[idgateway],
BOtradeTimestamp = vBOtradeTimestamp,
tradeType = rowstring[idtradeType],
SettlementCp = rowstring[idSettlementCp],
Value =
-side*
Math.Abs(double.Parse(rowstring[idtradedVolume],
CultureInfo.InvariantCulture)),
mty =
(Int64)
double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture),
deliveryDate = rowstring[idvalueDate] == ""
? Convert.ToDateTime(rowstring[idDate])
: Convert.ToDateTime(rowstring[idvalueDate]),
EntityLegalMalta = checkMalta
});
if (index%100 == 0) SaveDBChanges(ref db);
}
}
else
{
LogTextBox.AppendText("\r\n" + "Same Id exists in BO: " + id);
}
}
}
TradesParserStatus.Text = "DB updating";
try
{
db.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
foreach (DbEntityValidationResult validationErrors in dbEx.EntityValidationErrors)
{
foreach (DbValidationError validationError in validationErrors.ValidationErrors)
{
Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName,
validationError.ErrorMessage);
}
}
}
db.Database.ExecuteSqlCommand("CALL updateTradeNumbers()");
db.Dispose();
TradesParserStatus.Text = "Done";
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "BO trades uploading completed." +
(TimeEnd - TimeStart).ToString());
LogTextBox.AppendText("\r\n" + index.ToString() + " trades have been added.");
}
Console.WriteLine(result); // <-- For debugging use.
}
//todo get trades from DB BO
private List<Ctrade> getTradesFromDB (DateTime reportdate, List<string> cplist,bool removeReconciled){
var testexample = new EXANTE_Entities();
var prevreportdate = reportdate.AddDays(-1);
var ts = new TimeSpan(20, 00, 0);
prevreportdate = prevreportdate.Date + ts;
var nextdate = reportdate.AddDays(1);
var boTradeNumberlist = new List<long?>();
if (removeReconciled)
{
var boTradeNumbers = testexample.CpTrades.Where(
cptrade => cptrade.valid == 1 && cptrade.ReportDate >= reportdate.Date &&
cptrade.ReportDate < (nextdate.Date) && cptrade.BOTradeNumber != null)
.Select(cptrade => cptrade.BOTradeNumber);
foreach (string boTradeNumber in boTradeNumbers)
{
var templist = boTradeNumber.Split(';');
boTradeNumberlist.AddRange(templist.Select(s => (long?) Convert.ToInt64(s)));
}
// boTradeNumberlist.AddRange(boTradeNumbers.ToList().Select(s => (long?) Convert.ToInt64(s)));
}
/* var queryable = from ct in testexample.Ctrades
where ct.valid == 1 && ct.Date >= reportdate.Date && ct.Date < (nextdate.Date) &&
cplist.Contains(ct.cp_id) && !boTradeNumberlist.Contains(ct.tradeNumber)
select ct;*/
var queryable = from ct in testexample.Ctrades
where ct.valid == 1 && ct.RecStatus == false && ct.BOtradeTimestamp >= prevreportdate && ct.Date < (nextdate.Date)
//&&cplist.Contains(ct.cp_id)
select ct;
return queryable.ToList();
}
private static double GetValueccy(DateTime VMDate, string symbol)
{
var db = new EXANTE_Entities(_currentConnection);
int indexofOption = CustomIndexOf(symbol, '.', 3);
string key = "";
if (indexofOption > 0)
{
key = symbol.Substring(0, indexofOption) + ".";
}
else key = symbol;
List<int?> map =
(from ct in db.Mappings
where ct.valid == 1 && ct.Brocker == "OPEN" && ct.Type == "FORTS" && ct.BOSymbol == key
select ct.Round).ToList();
if ((map.Count > 0) && (map[0] == 1))
{
double? ccyrateFromDblinq =
(from ct in db.Prices
where
ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") &&
ct.Date == VMDate.Date
select ct.Price1).ToList()[0];
db.Dispose();
return (double) (1/ccyrateFromDblinq);
}
else
{
db.Dispose();
return 0;
}
}
private void TradesParser_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
var reportdate = new DateTime(2013, 04, 24);
var testexample = new EXANTE_Entities();
var reader = new StreamReader(openFileDialog2.FileName);
var allfromFile = new List<Ctrade>();
const int GMToffset = 4; //gmt offset from BO
const int nextdaystarthour = 19; //start new day for FORTS
const string template = "FORTS";
var nextdayvalueform = dateTimePicker1.Value;
var lineFromFile = reader.ReadLine();
TradesParserStatus.Text = "Processing";
if (lineFromFile != null)
{
var rowstring = lineFromFile.Split(Delimiter);
int idDate = 0,
idSymbol = 0,
idAccount = 0,
idqty = 0,
idprice = 0,
idside = 0,
idfees = 0,
iduser = 0,
idcurrency = 0,
idorderid = 0,
idbrokerTimeDelta = 0,
idexchangeOrderId = 0,
idcontractMultiplier = 0,
idtradeNumber = 0,
idcounterparty = 0,
idgateway = 0,
idtradeType=0,
idvalueDate=0;
for (var i = 0; i < rowstring.Length; i++)
{
switch (rowstring[i])
{
case "gwTime":
idDate = i;
break;
case "symbolId":
idSymbol = i;
break;
case "accountId":
idAccount = i;
break;
case "quantity":
idqty = i;
break;
case "price":
idprice = i;
break;
case "side":
idside = i;
break;
case "commission":
idfees = i;
break;
case "userId":
iduser = i;
break;
case "currency":
idcurrency = i;
break;
case "tradeNumber":
idtradeNumber = i;
break;
case "orderId":
idorderid = i;
break;
case "brokerTimeDelta":
idbrokerTimeDelta = i;
break;
case "exchangeOrderId":
idexchangeOrderId = i;
break;
case "contractMultiplier":
idcontractMultiplier = i;
break;
case "executionCounterparty":
idcounterparty = i;
break;
case "gatewayId":
idgateway = i;
break;
case "valueDate":
idvalueDate = i;
break;
case "tradeType":
idtradeType = i;
break;
default:
Console.WriteLine("Additional fields in the tr.file!");
break;
}
}
var index = 1;
var stringindex = Convert.ToString(reportdate.Year);
if (reportdate.Month < 10) stringindex = string.Concat(stringindex, "0");
stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Month));
if (reportdate.Day < 10) stringindex = string.Concat(stringindex, "0");
stringindex = string.Concat(stringindex, Convert.ToString(reportdate.Day));
var initialindex = Convert.ToInt64(stringindex);
var contractrow =
from ct in testexample.Contracts
where ct.valid == 1
select ct;
var contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate);
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
if (lineFromFile == null) continue;
rowstring = lineFromFile.Split(Delimiter);
DateTime? valuedate;
if (!contractdetails.TryGetValue(rowstring[idSymbol], out valuedate))
{
valuedate = new DateTime(2011, 01, 01);
//todo fill correct value date from file
var test = new Contract
{
id = rowstring[idSymbol],
Contract1 = rowstring[idSymbol],
Exchange = "Needtoupdate",
Type = "Needtoupdate",
Leverage =
(idcontractMultiplier > (rowstring.Length - 1)) ||
(rowstring[idcontractMultiplier] == "")
? 1
: double.Parse(rowstring[idcontractMultiplier], CultureInfo.InvariantCulture),
ValueDate = valuedate,//Convert.ToDateTime(rowstring[idvalueDate]),
Currency =
idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency],
Margin = 0,
FlatMargin = 0,
Canbesettled = true,
UpdateDate = DateTime.UtcNow,
commission = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture)/double.Parse(rowstring[idqty], CultureInfo.InvariantCulture),
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******"
};
testexample.Contracts.Add(test);
testexample.SaveChanges();
contractrow =
from ct in testexample.Contracts
where ct.valid == 1
select ct;
contractdetails = contractrow.ToDictionary(k => k.id, k => k.ValueDate);
}
var side = 1;
if (rowstring[idside] == "sell") side = -1;
var vBOtradeTimestamp = Convert.ToDateTime(rowstring[idDate]);
if (rowstring[idSymbol].IndexOf(template) > 0)
{
var fortscurrentDate = Convert.ToDateTime(rowstring[idDate]);
var initialdate = fortscurrentDate.ToShortDateString();
fortscurrentDate = fortscurrentDate.AddHours(24 - nextdaystarthour + GMToffset);
if (initialdate != fortscurrentDate.ToShortDateString())
fortscurrentDate = nextdayvalueform;
rowstring[idDate] = fortscurrentDate.ToShortDateString();
}
index++;
if (index > 0)
{
var trade_id = rowstring[idexchangeOrderId];
var account_id = rowstring[idAccount];
var Date = Convert.ToDateTime(rowstring[idDate]);
var symbol_id = rowstring[idSymbol];
var qty = rowstring[idqty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[idqty])*side
: double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side;
var price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture);
var cp_id = rowstring[idcounterparty];
var fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture);
var value_date = valuedate;//Convert.ToDateTime(rowstring[idvalueDate]),
var currency = idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency];
var tradeNumber = Convert.ToInt64(rowstring[idtradeNumber]);
var Timestamp = DateTime.UtcNow;
var valid = 1;
var username = rowstring[iduser];
var order_id = rowstring[idorderid];
var gatewayId = rowstring[idgateway];
var BOtradeTimestamp = vBOtradeTimestamp;
allfromFile.Add(new Ctrade
{
trade_id = rowstring[idexchangeOrderId],
account_id = rowstring[idAccount],
Date = Convert.ToDateTime(rowstring[idDate]),
symbol_id = rowstring[idSymbol],
qty = rowstring[idqty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[idqty])*side
: double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)* side,
price = double.Parse(rowstring[idprice], CultureInfo.InvariantCulture),
cp_id = rowstring[idcounterparty],
fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture),
value_date = valuedate,//Convert.ToDateTime(rowstring[idvalueDate]),
currency = idcontractMultiplier > (rowstring.Length - 1)
? "USD"
: rowstring[idcurrency],
tradeNumber = Convert.ToInt64(rowstring[idtradeNumber]),
Timestamp = DateTime.UtcNow,
valid = 1,
username = rowstring[iduser],
order_id = rowstring[idorderid],
gatewayId = rowstring[idgateway],
BOtradeTimestamp = vBOtradeTimestamp,
tradeType = rowstring[idtradeType],
deliveryDate = Convert.ToDateTime(rowstring[idvalueDate])
});
}
}
}
TradesParserStatus.Text = "DB updating";
// CheckUniqueTrades(allfromFile);
foreach (Ctrade tradeIndex in allfromFile)
{
testexample.Ctrades.Add(tradeIndex);
}
testexample.SaveChanges();
}
TradesParserStatus.Text = "Done";
Console.WriteLine(result); // <-- For debugging use.
}
private List<FullTrade> udpateVMforaccount(List<FullTrade> listofaccountpositions, DateTime VMDate,
string Brocker)
{
int i = 0;
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = VMDate.AddDays(1);
IQueryable<FT> listtodelete = from recon in db.FT
where recon.ReportDate >= VMDate.Date && recon.ReportDate < nextdate.Date
&& recon.Type.Contains("VM") && recon.cp.Contains(Brocker)
select recon;
db.FT.RemoveRange(listtodelete);
SaveDBChanges(ref db);
while (i < listofaccountpositions.Count)
{
FullTrade fullTrade = listofaccountpositions[i];
double valueccy = 0;
if (fullTrade.Value == 0)
{
double currentAtomOfVM = getatomofVM(fullTrade.Symbol, VMDate);
double priceFromDb = GetPrice(VMDate, fullTrade.Symbol);
double closeAtomOfVM = Math.Round(Math.Round(currentAtomOfVM*priceFromDb, 5), 2,
MidpointRounding.AwayFromZero);
fullTrade.Value =
Math.Round(
Math.Round(
fullTrade.Qty*
(closeAtomOfVM -
Math.Round(Math.Round(currentAtomOfVM*fullTrade.Price, 5), 2,
MidpointRounding.AwayFromZero)), 5), 2, MidpointRounding.AwayFromZero);
int j = i + 1;
while (j < listofaccountpositions.Count)
{
if ((listofaccountpositions[j].Value == 0) &&
(listofaccountpositions[j].Symbol == fullTrade.Symbol))
{
double t0 = currentAtomOfVM*listofaccountpositions[j].Price;
double t1 = Math.Round(currentAtomOfVM*listofaccountpositions[j].Price, 2,
MidpointRounding.AwayFromZero);
double t2 = closeAtomOfVM - t1;
double t3 = listofaccountpositions[j].Qty*t2;
double t4 = Math.Round(t3, 2);
listofaccountpositions[j].Value =
Math.Round(
Math.Round(
listofaccountpositions[j].Qty*
Math.Round(
Math.Round(
(closeAtomOfVM -
Math.Round(
Math.Round(currentAtomOfVM*listofaccountpositions[j].Price, 5), 2,
MidpointRounding.AwayFromZero)), 5), 2,
MidpointRounding.AwayFromZero), 5), 2, MidpointRounding.AwayFromZero);
}
j++;
}
}
i++;
valueccy = GetValueccy(VMDate, fullTrade.Symbol);
db.FT.Add(new FT
{
cp = Brocker,
brocker = Brocker,
ReportDate = VMDate,
account_id = fullTrade.Account,
timestamp = DateTime.Now,
symbol = fullTrade.Symbol,
ccy = "RUB",
value = fullTrade.Value,
valid = 1,
Type = "VM",
User = "******",
Comment = " ",
Reference = null,
ValueDate = VMDate,
TradeDate = VMDate,
BOSymbol = fullTrade.Symbol,
GrossPositionIndicator = null,
JOURNALACCOUNTCODE = null,
ValueCCY = -Math.Round(fullTrade.Value*valueccy, 2, MidpointRounding.AwayFromZero)
});
}
// db.SaveChanges();
SaveDBChanges(ref db);
db.Dispose();
return listofaccountpositions;
}
private static object getSymbolMap()
{
var testexample = new EXANTE_Entities();
var Mapping = from m in testexample.Mappings
where m.valid == 1 && m.Brocker=="ABN"
select m;
var result = Mapping.ToList();
testexample.Dispose();
return result;
}
private void updateFORTSccyrates()
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart + ": " + "Getting ccy prices from MOEX");
string Date = ABNDate.Value.ToString("yyyy-MM-dd");
// const string initialstring = "http://moex.com/ru/derivatives/currency-rate.aspx?currency=";
const string initialstring = "http://moex.com/export/derivatives/currency-rate.aspx?language=ru¤cy=";
// http://moex.com/export/derivatives/currency-rate.aspx?language=ru¤cy=USD/RUB&moment_start=2014-07-24&moment_end=2014-07-24
var listccy = new List<string>();
listccy.Add("USD/RUB");
listccy.Add("EUR/RUB");
var db = new EXANTE_Entities(_currentConnection);
foreach (string ccy in listccy)
{
string ccystring = initialstring + ccy + "&moment_start=" + Date + "&Date&moment_end=" + Date;
var doc = new XmlDocument();
doc.Load(ccystring);
XmlNode upnode = doc.SelectSingleNode("rtsdata");
string temp = "";
if (upnode != null)
{
temp = upnode.SelectSingleNode("rates").FirstChild.Attributes[1].Value;
}
db.Prices.Add(new Price
{
Ticker = ccy.Replace("/", ""),
Tenor =
DateTime.ParseExact(Date, "yyyy-MM-dd", CultureInfo.InvariantCulture),
Price1 = Convert.ToDouble(temp),
Date =
DateTime.ParseExact(Date, "yyyy-MM-dd", CultureInfo.InvariantCulture),
Type = "FORTS",
Timestamp = DateTime.Now,
Valid = 1,
Username = "******"
});
}
SaveDBChanges(ref db);
db.Dispose();
DateTime TimeEndUpdating = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEndUpdating + ": " + "CCY FORTS rates for " + Date +
" uploaded. Time:" + (TimeEndUpdating - TimeStart).ToString());
}
private static Dictionary<string, Map> getMap(string brocker)
{
var testexample = new EXANTE_Entities();
var mapfromDb = from m in testexample.Mappings
join c in testexample.Contracts on m.BOSymbol equals c.id
where m.Brocker == brocker
select new
{
m.BrockerSymbol,
m.BOSymbol,
m.MtyPrice,
m.MtyVolume,
m.Type,
m.Round,
c.ValueDate
};
var results = new Dictionary<string, Map>();
var mapfromDblist = mapfromDb.ToList();
foreach (var item in mapfromDblist)
{
var key = item.BrockerSymbol;
if (brocker != "BO") {
key = item.BrockerSymbol + item.Type;
}
if (item.Type == "FU") key = key + item.ValueDate.Value.ToShortDateString();
results.Add(key,new Map{BOSymbol = item.BOSymbol,
MtyPrice = item.MtyPrice,
MtyVolume = item.MtyVolume,
Round = item.Round,
Type = item.Type,
ValueDate = item.ValueDate,
});
}
return results;
}
private List<CpTrade> ExtractOptionTradesFromCliff(List<string> rowlist, Dictionary<string, Map> symbolmap)
{
var allfromfile = new List<CpTrade>();
var testexample = new EXANTE_Entities();
var cpfromDb = from cp in testexample.counterparties
select cp;
var cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id);
var reportdate = (DateTime)getDatefromString(rowlist[0].Substring(6, 8));
foreach (var row in rowlist)
{
var code = row.Substring(124, 2);
var typeoftrade = row.Substring(60, 2);
var tradedate = getDatefromString(row.Substring(554), true) ?? getDatefromString(row.Substring(562), true);
var symbol = row.Substring(66, 6).Trim();
var Counterparty = row.Substring(54, 6).Trim();
var valuedate = getDatefromString(row.Substring(73, 8).Trim());
var type = row.Substring(72, 1);
var strike = double.Parse( row.Substring(81, 8) + '.' + row.Substring(89, 7), CultureInfo.InvariantCulture);
var volumelong = double.Parse(row.Substring(128, 10) + '.' + row.Substring(138, 2), CultureInfo.InvariantCulture);
var volume = volumelong - double.Parse(row.Substring(141, 10) + '.' + row.Substring(151, 2), CultureInfo.InvariantCulture);
var price = double.Parse(row.Substring(247, 8) + '.' + row.Substring(255, 7), CultureInfo.InvariantCulture);
Map symbolvalue;
double? MtyVolume = 1;
double? MtyPrice = 1;
string BoSymbol = null;
var symbol_id = symbol +"."+type+ strike;
if (symbolmap.TryGetValue(symbol + "OP", out symbolvalue))
{
MtyVolume = symbolvalue.MtyVolume;
MtyPrice = symbolvalue.MtyPrice;
BoSymbol = symbolvalue.BOSymbol + "." + getLetterOfMonth(valuedate.Value.Month) + valuedate.Value.Year + "." + type + strike * MtyPrice;
}
var exchfee = double.Parse(row.Substring(153, 10) + '.' + row.Substring(163, 2), CultureInfo.InvariantCulture);
if (row.Substring(165, 1) == "D") exchfee = -exchfee;
var exchfeeccy = row.Substring(166, 3);
var fee = double.Parse(row.Substring(169, 10) + '.' + row.Substring(179, 2), CultureInfo.InvariantCulture);
if (row.Substring(181, 1) == "D") fee = -fee;
var clearingfeeccy = row.Substring(182, 3);
allfromfile.Add(new CpTrade
{
ReportDate = reportdate,
TradeDate = tradedate,
BrokerId = "ABN",
Symbol = symbol_id,
Type = typeoftrade,
Qty = volume * MtyVolume,
Price = price,
ValueDate = valuedate,
cp_id = getCPid(Counterparty, cpdic),
ExchangeFees = exchfee,
Fee = fee,
Id = null,
BOSymbol = BoSymbol,
BOTradeNumber = null,
value = null,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = null,
TypeOfTrade = code,
Comment = null,
ExchFeeCcy = exchfeeccy,
ClearingFeeCcy = clearingfeeccy
});
}
return allfromfile;
}
private List<CpTrade> ExtractTradesFromXml(Dictionary<string, Map> symbolmap)
{
//todo: unzip file
var doc = new XmlDocument();
//doc.Load(@"C:\20140214.xml");
doc.Load(openFileDialog2.FileName);
var testexample = new EXANTE_Entities();
var allfromfile = new List<CpTrade>();
var cpfromDb = from cp in testexample.counterparties
select cp;
var cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id);
//var results = products.ToDictionary(product => product.Id);
// var authors = Linkdoc.Root.Elements().Select(x => x.Element("UnsettledMovement"));
var row = -1;
{
//XmlNodeList nodes = doc.SelectNodes("/Transactions/AccountTransactions");
foreach (XmlNode mainnode in doc.DocumentElement.ChildNodes)
{
// var test = Mainnode.SelectNodes("UnsettledMovement/MovementCode[@Value = '01']");
foreach (XmlNode itemNode in mainnode.SelectNodes("UnsettledMovement"))
{
var list = itemNode.ChildNodes;
var MovementCode = itemNode.SelectSingleNode("MovementCode").InnerText;
// if (new [] {"01", "23", "24"}.Contains(MovementCode)){
row++;
var Pricemty = 1;
/* var selectSingleNode = itemNode.SelectSingleNode("ExchangeFee/Value");
var singleNode = itemNode.SelectSingleNode("ClearingFee/Value");
if (itemNode.SelectSingleNode("TransactionPriceCurrency/CurrencyPricingUnit") != null)
{
Pricemty = Convert.ToInt32(itemNode.SelectSingleNode("TransactionPriceCurrency/CurrencyPricingUnit").InnerText);
}*/
/* todo Решить задачу с комиссиями
var ExchangeFees = selectSingleNode != null && (selectSingleNode.InnerText == "D")
? -1*Convert.ToDouble(itemNode.SelectSingleNode("ExchangeFee/Value").InnerText)
: Convert.ToDouble(itemNode.SelectSingleNode("ExchangeFee/Value").InnerText);
var Fee = singleNode != null && (singleNode.InnerText == "D")
? -1*Convert.ToDouble(itemNode.SelectSingleNode("ClearingFee/Value").InnerText)
: Convert.ToDouble(itemNode.SelectSingleNode("ClearingFee/Value").InnerText)*/
var typeOftrade = GetTypeOfTradeFromXml(itemNode);
if (typeOftrade == "FW" || typeOftrade == "FX")
{
if (itemNode.SelectSingleNode("TransactionPriceCurrency/CurrencyPricingUnit") !=
null)
{
Pricemty = 10000/Convert.ToInt32(itemNode.SelectSingleNode(
"TransactionPriceCurrency/CurrencyPricingUnit").InnerText);
}
}
var symbolid = itemNode.SelectSingleNode("Product/Symbol").InnerText + typeOftrade;
Map symbolvalue;
var bosymbol = "";
if (symbolmap.TryGetValue(symbolid, out symbolvalue))
{
bosymbol = symbolvalue.BOSymbol;
}
else
{
bosymbol = "";
}
allfromfile.Add(new CpTrade
{
ReportDate =
DateTime.ParseExact(itemNode.SelectSingleNode("ProcessingDate").InnerText,
"yyyyMMdd", CultureInfo.CurrentCulture),
TradeDate = (itemNode.SelectSingleNode("TimeStamp") != null)
? Convert.ToDateTime(
itemNode.SelectSingleNode("TimeStamp").InnerText)
: DateTime.ParseExact(
itemNode.SelectSingleNode("TransactionDate").InnerText,
"yyyyMMdd", CultureInfo.CurrentCulture),
BrokerId = "test",
Symbol = itemNode.SelectSingleNode("Product/Symbol").InnerText,
Type = typeOftrade,
Qty = (itemNode.SelectSingleNode("QuantityShort") == null)
? Convert.ToInt64(itemNode.SelectSingleNode("QuantityLong").InnerText)
: -1*Convert.ToInt64(itemNode.SelectSingleNode("QuantityShort").InnerText),
Price = (itemNode.SelectSingleNode("TransactionPrice") != null)
? (double)
decimal.Round(
Convert.ToDecimal(
itemNode.SelectSingleNode("TransactionPrice").InnerText)/
Pricemty, 8)
: 0,
ValueDate = GetValueDate(itemNode),
cp_id =
getCPid(
itemNode.SelectSingleNode("OppositeParty/OppositePartyCode").InnerText,
cpdic),
ExchangeFees = 0,
Fee = 0,
Id = null,
BOSymbol = (bosymbol == "") ? null : bosymbol,
BOTradeNumber = null,
value = (itemNode.SelectSingleNode("EffectiveValue/ValueDC") != null)
? (itemNode.SelectSingleNode("EffectiveValue/ValueDC").InnerText ==
"D")
? -1*
Convert.ToDouble(
itemNode.SelectSingleNode("EffectiveValue/Value")
.InnerText)
: Convert.ToDouble(
itemNode.SelectSingleNode("EffectiveValue/Value")
.InnerText)
: 0,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = null,
TypeOfTrade = MovementCode,
Comment = (itemNode.SelectSingleNode("TransactionOrigin") != null)
? itemNode.SelectSingleNode("TransactionOrigin").InnerText
: ""
});
// var cp_id = itemNode.SelectSingleNode("OppositePartyCode").InnerText;
// var value = itemNode.SelectSingleNode("").InnerText;
//if 01 }
}
foreach (XmlNode itemNode in mainnode.SelectNodes("FutureMovement"))
{
var list = itemNode.ChildNodes;
var MovementCode = itemNode.SelectSingleNode("MovementCode").InnerText;
// if (new[] { "01", "23", "24" }.Contains(MovementCode)){
var Pricemty = 1;
var price = Convert.ToDouble(itemNode.SelectSingleNode("TransactionPrice").InnerText)/
Pricemty;
var qty = (itemNode.SelectSingleNode("QuantityShort") == null)
? Convert.ToInt64(itemNode.SelectSingleNode("QuantityLong").InnerText)
: -1*Convert.ToInt64(itemNode.SelectSingleNode("QuantityShort").InnerText);
var symbolid = itemNode.SelectSingleNode("Product/Symbol").InnerText + "FU" +
Convert.ToDateTime(GetValueDate(itemNode)).ToShortDateString();
Map symbolvalue;
var bosymbol = "";
if (symbolmap.TryGetValue(symbolid, out symbolvalue))
{
bosymbol = symbolvalue.BOSymbol;
}
else
{
bosymbol = "";
}
allfromfile.Add(new CpTrade
{
ReportDate =
DateTime.ParseExact(itemNode.SelectSingleNode("ProcessingDate").InnerText,
"yyyyMMdd", CultureInfo.CurrentCulture),
TradeDate = Convert.ToDateTime(itemNode.SelectSingleNode("TimeStamp").InnerText),
BrokerId = "test",
Symbol = itemNode.SelectSingleNode("Product/Symbol").InnerText,
Type = GetTypeOfTradeFromXml(itemNode),
Qty = qty,
Price = price,
ValueDate = GetValueDate(itemNode),
cp_id =
getCPid(
itemNode.SelectSingleNode("OppositeParty/OppositePartyCode").InnerText,
cpdic),
ExchangeFees = 0,
Fee = 0,
Id = null,
BOSymbol = bosymbol,
BOTradeNumber = null,
value =
-Convert.ToInt64(itemNode.SelectSingleNode("Tradingunit").InnerText == "D")*
price*qty,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = null,
TypeOfTrade = MovementCode,
Comment = (itemNode.SelectSingleNode("TransactionOrigin") != null)
? itemNode.SelectSingleNode("TransactionOrigin").InnerText
: ""
});
//if 01 }
}
}
}
return allfromfile;
}
private List<CpTrade> ExtractTradesFromCliff(List<string> rowlist, Dictionary<string, Map> symbolmap)
{
var allfromfile = new List<CpTrade>();
var testexample = new EXANTE_Entities();
var cpfromDb = from cp in testexample.counterparties
select cp;
var cpdic = cpfromDb.ToDictionary(k => k.Name, k => k.cp_id);
var reportdate = (DateTime)getDatefromString(rowlist[0].Substring(6, 8));
foreach (var row in rowlist)
{
var typeoftrade = row.Substring(60, 2);
var tradedate = getDatefromString(row.Substring(582),true) ?? getDatefromString(row.Substring(295), true);
var symbol = row.Substring(66, 6).Trim();
var type = row.Substring(60, 2);
if (row.Substring(405, 4) == "FW-E")
{
type = "FW-E";
}
Map symbolvalue;
double? MtyVolume = 1;
double? MtyPrice = 1;
string BoSymbol = null;
var symbol_id = symbol+type;
var valuedate = getDatefromString(row.Substring(303)) ?? getDatefromString(row.Substring(72));
if (typeoftrade == "FU")
{
symbol_id = symbol_id + Convert.ToDateTime(valuedate).ToShortDateString();
}
if (symbolmap.TryGetValue(symbol_id, out symbolvalue))
{
MtyVolume = symbolvalue.MtyVolume;
MtyPrice = symbolvalue.MtyPrice;
BoSymbol = symbolvalue.BOSymbol;
}
var exchfee = double.Parse(row.Substring(137, 10) + '.' + row.Substring(147, 2), CultureInfo.InvariantCulture);
if (row.Substring(149, 1) == "D") exchfee = -exchfee;
var exchfeeccy = row.Substring(150, 3);
var fee = double.Parse(row.Substring(153, 10) + '.' + row.Substring(163, 2), CultureInfo.InvariantCulture);
if (row.Substring(165, 1) == "D") fee = -fee;
var clearingfeeccy = row.Substring(166, 3);
double value;
double transacPrice;
if (typeoftrade != "FU")
{
value = double.Parse(row.Substring(276, 16) + '.' + row.Substring(292, 2), CultureInfo.InvariantCulture);
if (row.Substring(294, 1) == "D") value = -value;
transacPrice = Math.Round(double.Parse(row.Substring(360, 8) + "." + row.Substring(368, 7), CultureInfo.InvariantCulture)*(double) MtyPrice, 10);
}
else
{
transacPrice = Math.Round(double.Parse(row.Substring(230, 8) + "." + row.Substring(238, 7), CultureInfo.InvariantCulture) * (double)MtyPrice, 10);
value = -Math.Round(GetValueFromCliff(row.Substring(112)) * (double)MtyVolume * transacPrice, 10);
}
allfromfile.Add(new CpTrade
{
ReportDate = reportdate,
TradeDate = typeoftrade=="FU"
? getDatefromString(row.Substring(496), true)
: getDatefromString(row.Substring(582), true) ?? getDatefromString(row.Substring(295), true),
BrokerId = "ABN",
Symbol = symbol,
Type = (row.Substring(405, 4) == "FW-E")
? "FW-E"
: type,
Qty = GetValueFromCliff(row.Substring(112))*MtyVolume,
Price = transacPrice,
ValueDate = valuedate,
cp_id =getCPid(row.Substring(54,6).Trim(), cpdic),
ExchangeFees = exchfee,
Fee = fee,
Id = null,
BOSymbol = BoSymbol,
BOTradeNumber = null,
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = null,
TypeOfTrade = row.Substring(108,2),
Comment = null,
ExchFeeCcy = exchfeeccy,
ClearingFeeCcy = clearingfeeccy
});
}
return allfromfile;
}