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 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 double UpdateFortsPrices(DateTime fortsDate, string currentInstrument)
{
const string initialstring = "http://moex.com/ru/derivatives/contractresults.aspx?code=";
// var listCurrentInstruments = getFORTSinstrument(fortsDate);
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, Map> map = getSymbolMap("OPEN", "FORTS");
var list = new List<string>();
list.Add("><tr valign=top class=tr0><td>");
list.Add("><td align='right' nowrap>");
list.Add("><tr valign=top class=tr1><td>");
list.Add("\xa0");
list.Add("\r");
list.Add("\n");
list.Add("><tr class=tr1 align=right><td>");
list.Add("><tr class=tr0 align=right><td>");
double pricefw = 0;
Map symbolvalue;
// var indexofOption = currentInstrument.IndexOf("FORTS")+11;
int indexofOption = CustomIndexOf(currentInstrument, '.', 3);
string key = "";
if (indexofOption > 0)
{
key = currentInstrument.Substring(0, indexofOption + 1);
}
else key = currentInstrument;
if (!map.TryGetValue(key, out symbolvalue))
{
LogTextBox.AppendText("\r\n" + "New Symbol: " + key);
}
else
{
string mappingsymbol = symbolvalue.BOSymbol;
var vd = (DateTime) symbolvalue.ValueDate;
if (indexofOption > 0)
{
mappingsymbol = mappingsymbol + currentInstrument[indexofOption + 1] + "A " +
currentInstrument.Substring(indexofOption + 2);
//+ "M" + vd.ToString("ddMMyy") + currentInstrument[indexofOption + 1] +"A " + currentInstrument.Substring(indexofOption + 2);
}
List<List<string>> webpage = GetPage(initialstring + mappingsymbol, "/tr", "</td", list);
pricefw = getpricefromhtml(webpage, fortsDate);
if (pricefw != -1)
{
db.Prices.Add(new Price
{
Ticker = currentInstrument,
Tenor = vd,
Price1 = pricefw,
Date = fortsDate,
Type = "FORTS",
Timestamp = DateTime.Now,
Valid = 1,
Username = "******"
});
}
db.SaveChanges();
}
db.Dispose();
return pricefw;
}
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 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 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 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 void button3_Click_1(object sender, EventArgs e)
{
TradesParserStatus.Text = "Processing";
var db = new EXANTE_Entities(_currentConnection);
DialogResult result = openFileDialog2.ShowDialog();
DateTime reportDate = ABNDate.Value;
if (result == DialogResult.OK)
{
foreach (string oFilename in openFileDialog2.FileNames)
{
DateTime TimeUpdateBalanceStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeUpdateBalanceStart + ": " + "start FT Balance uploading for ");
var ObjExcel =
new Application();
//Открываем книгу.
Workbook ObjWorkBook = ObjExcel.Workbooks.Open(oFilename,
0, false, 5, "", "",
false,
XlPlatform
.xlWindows,
"",
true, false, 0, true,
false, false);
//Выбираем таблицу(лист).
Worksheet ObjWorkSheet;
ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Sheet1"];
Range xlRange = ObjWorkSheet.UsedRange;
IFormatProvider theCultureInfo = new CultureInfo("en-GB", true);
int jj = 1;
dynamic account = xlRange.Cells[5 + jj, 2].value2.ToString();
int idReportDate = 1,
idLabel = 2,
idPrice = 3,
idOpType = 4,
idDebit = 5,
idCredit = 6;
string ccy = "";
ccy = xlRange.Cells[8 + jj, 2].value2;
LogTextBox.AppendText(ccy);
int i = 13 + jj;
int index = 0;
dynamic tempreportdate = xlRange.Cells[i, idReportDate].value2;
if (tempreportdate != null)
{
reportDate = DateTime.ParseExact(xlRange.Cells[i, idReportDate].value2.ToString(), "dd/MM/yyyy",
theCultureInfo);
}
else
{
reportDate = ABNDate.Value.Date;
}
/* var listtodelete = from ft in db.FT
where ft.ccy == ccy && ft.cp == "ADSS" && reportDate.Date == ft.ReportDate
select ft;
db.FT.RemoveRange(listtodelete);
db.SaveChanges();*/
CleanOldValue(db, ccy, "ADSS", reportDate.Date);
while (xlRange.Cells[i, 1].value2 != null)
{
string type = "";
if (xlRange.Cells[i, idOpType].value2 == "Comm.")
{
type = "Commission";
}
else
{
if (xlRange.Cells[i, idOpType].value2 == "Cash")
{
type = "Cash";
}
else
{
type = xlRange.Cells[i, idLabel].value2;
type = type.Substring(type.IndexOf('/') + 1, 4);
if (type == "ESWP") type = "Swap";
if (type == "ADSS" && xlRange.Cells[i, idOpType].value2 == "Trade") type = "Trade";
}
}
// reportDate = DateTime.ParseExact(xlRange.Cells[i, idReportDate].value2.ToString(), "dd/MM/yyyy",
// theCultureInfo);
/* var t = xlRange.Cells[i, idCredit].Text.ToString();
t = xlRange.Cells[i, idCredit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idCredit].Text.ToString().Replace(" ", "")) : 0;
var t3 = xlRange.Cells[i, idDebit].Text.ToString();
t3=t3.Replace(" ", "");
var t2 = xlRange.Cells[i, idDebit].value2 != null ? Convert.ToDouble(xlRange.Cells[i, idDebit].Text.ToString().Replace(" ", "")) : 0;
t = t - t2;*/
db.FT.Add(new FT
{
ReportDate = reportDate.Date,
cp = "ADSS",
account_id = account,
ccy = ccy,
Type = "FT",
symbol = type,
value =
(xlRange.Cells[i, idCredit].value2 != null
? Convert.ToDouble(xlRange.Cells[i, idCredit].Text.ToString().Replace(" ", ""))
: 0) -
(xlRange.Cells[i, idDebit].value2 != null
? Convert.ToDouble(xlRange.Cells[i, idDebit].Text.ToString().Replace(" ", ""))
: 0),
Comment = xlRange.Cells[i, idLabel].value2 + ";" + xlRange.Cells[i, idPrice].value2,
timestamp = DateTime.UtcNow,
valid = 1,
User = "******"
});
i++;
SaveDBChanges(ref db);
index++;
}
dynamic OpenCash = Convert.ToDouble(xlRange.Cells[10 + jj, 2].value2);
dynamic CloseCash = Convert.ToDouble(xlRange.Cells[i + 1, 2].value2);
double? OpenCashFromDb = GetCloseCashFromPrevDate(db, ccy, "ADSS");
string comment = "";
if (Math.Abs(OpenCash - OpenCashFromDb) > 0.01)
{
LogTextBox.AppendText("\r\n" + "Inccorect open cash for " + ccy + " " +
reportDate.ToShortDateString());
comment = "Discrepancy in open cash and close cash of previous day";
}
var movements = (from ft in db.FT
where ft.ccy == ccy && ft.cp == "ADSS" && reportDate.Date == ft.ReportDate
group ft by new {ft.symbol}
into g
select new
{
type = g.Key.symbol,
Sum = g.Sum(t => t.value)
}).ToList();
double sum = 0;
double sumswap = 0;
double sumtrade = 0;
double sumfee = 0;
double sumcash = 0;
foreach (var movement in movements)
{
sum = sum + movement.Sum.Value;
switch (movement.type)
{
case "Swap":
sumswap = movement.Sum.Value;
break;
case "Trade":
sumtrade = movement.Sum.Value;
break;
case "Commission":
sumfee = movement.Sum.Value;
break;
case "Cash":
sumcash = movement.Sum.Value;
break;
}
}
if (Math.Abs(CloseCash - OpenCash - sum) > 0.01)
{
LogTextBox.AppendText("\r\n" + "Inccorect difference between open and close cash for " + ccy +
" " +
reportDate.ToShortDateString());
comment = comment + ";Inccorect difference between open and close cash";
}
IQueryable<ADSSCashGroupped> todelete = from ft in db.ADSSCashGroupped
where
ft.Currency == ccy && reportDate.Date == ft.ReportDate &&
ft.Cp == "ADSS"
select ft;
db.ADSSCashGroupped.RemoveRange(todelete);
db.SaveChanges();
db.ADSSCashGroupped.Add(new ADSSCashGroupped
{
ClosingCash = Math.Round(CloseCash, 2),
Commission = Math.Round(sumfee, 2),
Currency = ccy,
Deposit = Math.Round(sumcash, 2),
OpeningCash = OpenCash,
ReportDate = reportDate.Date,
SWAPs = Math.Round(sumswap, 2),
Trades = Math.Round(sumtrade, 2),
comment = comment,
Cp = "ADSS"
});
SaveDBChanges(ref db);
DateTime TimeFutureParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeFutureParsing.ToLongTimeString() + ": " +
"FT parsing completed for " + ccy + ":" + oFilename + "." + "\r\n" + index +
" items have been uploaded. Time: " +
(TimeFutureParsing - TimeUpdateBalanceStart).ToString() + "s");
ObjWorkBook.Close();
ObjExcel.Quit();
Marshal.FinalReleaseComObject(ObjWorkBook);
Marshal.FinalReleaseComObject(ObjExcel);
}
}
AddCcyFromPreviousReports(db, "ADSS");
SaveDBChanges(ref db);
db.Dispose();
}
private void button5_Click(object sender, EventArgs e)
{
// const string conStr = "https://backoffice-recon.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade";
// var token = GetToken("https://authdb-recon.exante.eu/api/1.0/auth/session", "backoffice");
const string conStr = "https://backoffice.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade";
string token = GetToken("https://authdb.exante.eu/api/1.0/auth/session", "backoffice");
DateTime reportdate = ABNDate.Value;
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = reportdate.AddDays(1);
var cptradefromDb = (from ft in db.FT
where ft.valid == 1 && ft.brocker == "OPEN" &&
ft.Type == "AF" &&
ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date) &&
ft.ValueCCY != 0
&& ft.Reference == null
group ft by new {ft.account_id, ft.symbol, ft.ccy}
into g
select new
{
g.Key.account_id,
g.Key.symbol,
BOSymbol = g.Key.symbol,
value = g.Sum(t => t.value),
g.Key.ccy,
ValueCCY = g.Sum(t => t.ValueCCY)
}).ToList();
int tradesqty = 0;
foreach (var VARIABLE in cptradefromDb)
{
var p = new FTjson();
p.operationType = "COMMISSION";
p.comment = "Additional fees from cp: " + VARIABLE.BOSymbol + " for " + reportdate.ToShortDateString();
p.asset = VARIABLE.ccy;
p.symbolId = VARIABLE.BOSymbol;
// p.asset = VARIABLE.counterccy;
p.accountId = VARIABLE.account_id;
p.amount = Math.Round((double) VARIABLE.value, 2).ToString();
p.timestamp = reportdate.ToString("yyyy-MM-dd HH:mm:ss");
string requestFTload = JsonConvert.SerializeObject(p);
if (!SendJson(requestFTload, conStr + VARIABLE.account_id + "/transaction", token))
// if (!SendJson(requestFTload, conStr + "TST1149.TST" + "/transaction", token))
// if (!SendJson(requestFTload, conStr + "ZAM1452.001" + "/transaction", token))
{
LogTextBox.AppendText("\r\n Error in sending Left side VM to BO for : " + VARIABLE.account_id + " " +
VARIABLE.symbol);
}
}
if (tradesqty > 0)
{
db.SaveChanges();
db.Dispose();
LogTextBox.AppendText("\r\n Uploaded trades for " + reportdate.ToShortDateString() + ": " +
tradesqty.ToString() + "/" + cptradefromDb.Count);
}
}
private void button10_Click(object sender, EventArgs e)
{
var db = new EXANTE_Entities(_currentConnection);
var reportdate = new DateTime(2012, 05, 14);
var prevdate = new DateTime(2012, 05, 04);
DateTime TimeStart = DateTime.Now;
List<Ftbo> ftboitems =
(from ct in db.Ftboes
where
ct.botimestamp >= prevdate && ct.botimestamp <= reportdate &&
(ct.symbolId == "" || ct.symbolId == null) && ct.tradeNumber != null
select ct).ToList();
//ToDictionary(k => (k.tradeNumber.ToString()+k.gatewayId), k => k);
int index = 0;
Dictionary<string, string> ctradeitems =
(from ct in db.Ctrades
where ct.BOtradeTimestamp <= reportdate.Date && ct.BOtradeTimestamp >= prevdate.Date
select ct).ToDictionary(k => (k.tradeNumber.ToString() + k.gatewayId), k => k.symbol_id);
foreach (Ftbo ftbo in ftboitems)
{
string symbolid;
if (ctradeitems.TryGetValue(ftbo.tradeNumber.ToString() + ftbo.gatewayId, out symbolid))
{
ftbo.symbolId = symbolid;
db.Ftboes.Attach(ftbo);
db.Entry(ftbo).State = (EntityState)System.Data.Entity.EntityState.Modified;
index++;
}
else
{
LogTextBox.AppendText("\r\n" + "Didn't find trade for this id:" + ftbo.id + " " + ftbo.tradeNumber);
}
}
db.SaveChanges();
// var n = queryable.Count();
// var m = queryable2.Count();
DateTime TimeFutureParsing = DateTime.Now;
db.Dispose();
LogTextBox.AppendText("\r\n" + TimeFutureParsing.ToLongTimeString() + " Updating symbol completed for " +
index + " items. Time: " + (TimeFutureParsing - TimeStart).ToString() + "s");
}
private void button29_Click(object sender, EventArgs e)
{
const string conStr = "https://backoffice.exante.eu:443/api/v1.5/accounts/"; // "ZAM1452.001/trade";
string token = GetToken("https://authdb.exante.eu/api/1.0/auth/session", "backoffice");
DateTime reportdate = ABNDate.Value;
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = reportdate.AddDays(1);
var cptradefromDb = (from ft in db.FT
where ft.valid == 1 &&
ft.cp == "Manual" &&
ft.ReportDate >= reportdate.Date && ft.ReportDate < (nextdate.Date)
&& ft.Posted == null
select new
{
ft.account_id,
ft.symbol,
BOSymbol = ft.symbol,
ft.value,
type = ft.Type,
ft.ccy,
ft.counterccy,
ft.ValueCCY,
ft.Comment,
tradeDate = ft.TradeDate,
id = ft.fullid
}).ToList();
int tradesqty = 0;
foreach (var VARIABLE in cptradefromDb)
{
var p = new FTjson();
p.operationType = VARIABLE.type;
p.comment = VARIABLE.Comment;
p.asset = VARIABLE.ccy;
p.symbolId = VARIABLE.BOSymbol;
p.accountId = VARIABLE.account_id;
p.amount = Math.Round((double) VARIABLE.value, 2).ToString();
p.timestamp = VARIABLE.tradeDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
string requestFTload = JsonConvert.SerializeObject(p);
if (!SendJson(requestFTload, conStr + VARIABLE.account_id + "/transaction", token))
{
LogTextBox.AppendText("\r\n Error in sending FT for : " + VARIABLE.id);
}
else
{
db.Database.ExecuteSqlCommand("update FT SET Posted= NOW() where fullid=" + VARIABLE.id);
}
}
if (tradesqty > 0)
{
db.SaveChanges();
db.Dispose();
LogTextBox.AppendText("\r\n Uploaded FT for " + reportdate.ToShortDateString() + ": " +
tradesqty.ToString() + "/" + cptradefromDb.Count);
}
}
private static void CleanOldValue(EXANTE_Entities db, string ccy, string account, DateTime reportDate)
{
IQueryable<FT> listtodelete = from ft in db.FT
where ft.ccy == ccy && ft.cp == account && reportDate.Date == ft.ReportDate
select ft;
db.FT.RemoveRange(listtodelete);
db.SaveChanges();
}
private void ABNReconButtonClick(object sender, EventArgs e)
{
DateTime reportdate = ABNDate.Value; //todo Get report date from xml Processing date
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, Map> symbolmap = getMap("ABN");
TradesParserStatus.Text = "Processing";
if (noparsingCheckbox.Checked)
{
RecProcess(reportdate, "ABN");
}
else
{
var allfromfile = new List<CpTrade>();
var futtrades = new List<CpTrade>();
DialogResult result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK)
{
if (CliffCheckBox.Checked)
{
Dictionary<string, List<string>> cliffdict = LoadCliff(openFileDialog2.FileName, reportdate);
List<string> rowlist;
DateTime TimeUpdateBalanceStart = DateTime.Now;
if (cliffdict.TryGetValue("610", out rowlist)) updateBalance(rowlist, reportdate);
DateTime TimeFutureParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeFutureParsing.ToLongTimeString() + ": " +
"Update Balance Completed. Time:" +
(TimeFutureParsing - TimeUpdateBalanceStart).ToString() + "s");
if (cliffdict.TryGetValue("310", out rowlist))
allfromfile = ExtractTradesFromCliff(rowlist, symbolmap);
DateTime TimeStockParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStockParsing.ToLongTimeString() + ": " +
"Future parsing Completed. Time:" +
(TimeStockParsing - TimeFutureParsing).ToString() + "s");
if (cliffdict.TryGetValue("410", out rowlist))
allfromfile.AddRange(ExtractTradesFromCliff(rowlist, symbolmap));
DateTime TimeOptionParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeOptionParsing.ToLongTimeString() + ": " +
"Stock parsing Completed. Time:" +
(TimeOptionParsing - TimeStockParsing).ToString() + "s");
if (cliffdict.TryGetValue("210", out rowlist))
allfromfile.AddRange(ExtractOptionTradesFromCliff(rowlist, symbolmap));
DateTime TimeEndOptionParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEndOptionParsing.ToLongTimeString() + ": " +
"Option parsing Completed. Time:" +
(TimeEndOptionParsing - TimeOptionParsing).ToString() + "s");
GetABNPos(cliffdict, reportdate);
DateTime TimePositionParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeOptionParsing.ToLongTimeString() + ": " +
"Position parsing Completed. Time:" +
(TimePositionParsing - TimeEndOptionParsing).ToString() + "s");
if (cliffdict.TryGetValue("600", out rowlist))
{
reportdate = getcashmovements(rowlist);
}
DateTime TimeFTParsing = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeFutureParsing.ToLongTimeString() + ": " +
"FT parsing completed for " + reportdate.ToShortDateString() + ". Time:" +
(TimeFTParsing - TimePositionParsing).ToString() + "s");
}
else
{
allfromfile = ExtractTradesFromXml(symbolmap);
}
foreach (CpTrade tradeIndex in allfromfile)
{
db.CpTrades.Add(tradeIndex);
}
db.SaveChanges();
allfromfile = allfromfile.Where(s => s.TypeOfTrade == "01").ToList();
DateTime TimeStartReconciliation = DateTime.Now;
AbnRecon(reportdate, allfromfile, "ABN");
DateTime TimeEndReconciliation = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEndReconciliation.ToLongTimeString() + ": " +
"Reconciliation completed. Time:" +
(TimeEndReconciliation - TimeStartReconciliation).ToString() + "s");
}
}
TradesParserStatus.Text = "Done";
}
private static void AddCcyFromPreviousReports(EXANTE_Entities db, string cp)
{
DateTime reportDate = (from ft in db.ADSSCashGroupped
where ft.Cp == cp
orderby ft.ReportDate descending
select ft.ReportDate).ToList()[0];
DateTime prevreportDate = (from ft in db.ADSSCashGroupped
where ft.ReportDate < reportDate.Date && ft.Cp == cp
orderby ft.ReportDate descending
select ft.ReportDate).ToList()[0];
List<string> listCcyReportdate = (from ft in db.ADSSCashGroupped
where ft.ReportDate == reportDate.Date && ft.Cp == cp
select ft.Currency).ToList();
List<ADSSCashGroupped> PreviousReport = (from ft in db.ADSSCashGroupped
where ft.ReportDate == prevreportDate.Date && ft.Cp == cp
select ft).ToList();
foreach (ADSSCashGroupped adssCashGroupped in PreviousReport)
{
if (!listCcyReportdate.Any(a => a == adssCashGroupped.Currency))
{
db.ADSSCashGroupped.Add(new ADSSCashGroupped
{
ClosingCash = Math.Round(adssCashGroupped.ClosingCash.Value, 2),
Commission = 0,
Currency = adssCashGroupped.Currency,
Deposit = 0,
Cp = cp,
OpeningCash = adssCashGroupped.ClosingCash.Value,
ReportDate = reportDate.Date,
SWAPs = 0,
Trades = 0,
comment = "Copied from " + prevreportDate.ToShortDateString()
});
}
}
db.SaveChanges();
}
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 void CFHReconciliation(object sender, EventArgs e)
{
DateTime reportdate = ABNDate.Value; //todo Get report date from xml Processing date
TradesParserStatus.Text = "Processing";
var db = new EXANTE_Entities(_currentConnection);
if (!noparsingCheckbox.Checked)
{
List<InitialTrade> lInitTrades = CFHParsing();
List<CpTrade> lCptrades = OpenConverting(lInitTrades, "CFH");
foreach (CpTrade cptrade in lCptrades)
{
db.CpTrades.Add(cptrade);
}
db.SaveChanges();
}
else
{
DateTime nextdate = reportdate.AddDays(1);
Dictionary<string, Map> symbolmap = getMapping("CFH");
IQueryable<CpTrade> cptradefromDb = from cptrade in db.CpTrades
where cptrade.valid == 1 && cptrade.BrokerId == "CFH" &&
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)
{
if (cpTrade.BOSymbol == null && symbolmap.ContainsKey(cpTrade.Symbol))
{
Map map = symbolmap[cpTrade.Symbol];
cpTrade.BOSymbol = map.BOSymbol;
cpTrade.Price = cpTrade.Price*map.MtyPrice;
cpTrade.Qty = cpTrade.Qty*map.MtyVolume;
cpTrade.value = cpTrade.value*map.Leverage;
if (contractdetails.ContainsKey(map.BOSymbol))
{
cpTrade.ValueDate = contractdetails[map.BOSymbol].ValueDate;
}
else
{
cpTrade.ValueDate = map.ValueDate;
}
db.CpTrades.Attach(cpTrade);
db.Entry(cpTrade).State = (EntityState)System.Data.Entity.EntityState.Modified;
}
}
SaveDBChanges(ref db);
}
RecProcess(reportdate, "CFH");
TradesParserStatus.Text = "Done";
Console.WriteLine(""); // <-- For debugging use. */
}
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 DateTime ExtractPositionFromCliff(List<string> rowlist)
{
Dictionary<string, Map> symbolmap = getMap("ABN");
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 reportdate = (DateTime) getDatefromString(rowlist[0].Substring(6, 8));
foreach (string row in rowlist)
{
string type = row.Substring(60, 2);
string symbol = row.Substring(66, 6).Trim();
string symbol_id = symbol + type;
DateTime? valuedate;
double transacPrice = 0;
int round = 10;
DateTime tradedate;
double qty;
string ccy;
string optiontype = null;
double? strike = null;
if (type == "FU")
{
valuedate = getDatefromString(row.Substring(72, 8));
symbol_id = symbol_id + Convert.ToDateTime(valuedate).ToShortDateString();
tradedate = (DateTime) getDatefromString(row.Substring(183, 8));
transacPrice =
Math.Round(
double.Parse(row.Substring(108, 8) + "." + row.Substring(116, 7),
CultureInfo.InvariantCulture), round);
qty = GetValueFromCliff(row.Substring(124));
ccy = row.Substring(105, 3);
}
else
{
if (type == "OP")
{
valuedate = getDatefromString(row.Substring(73, 8));
tradedate = (DateTime) getDatefromString(row.Substring(184, 8));
strike =
Math.Round(
double.Parse(row.Substring(81, 8) + "." + row.Substring(89, 7),
CultureInfo.InvariantCulture), round);
transacPrice =
Math.Round(
double.Parse(row.Substring(169, 8) + "." + row.Substring(177, 7),
CultureInfo.InvariantCulture), round);
ccy = row.Substring(121, 3);
qty = GetValueFromCliff(row.Substring(145));
optiontype = row.Substring(72, 1);
}
else
{
valuedate = getDatefromString(row.Substring(72, 8));
tradedate = (DateTime) getDatefromString(row.Substring(209, 8));
transacPrice =
Math.Round(
double.Parse(row.Substring(182, 8) + "." + row.Substring(190, 7),
CultureInfo.InvariantCulture), round);
ccy = row.Substring(117, 3);
qty = GetValueFromCliff(row.Substring(120));
}
}
Map symbolvalue;
double? MtyVolume = 1;
double? MtyPrice = 1;
string BoSymbol = null;
if (symbolmap.TryGetValue(symbol_id, out symbolvalue))
{
MtyVolume = symbolvalue.MtyVolume;
MtyPrice = symbolvalue.MtyPrice;
BoSymbol = symbolvalue.BOSymbol;
round = (int) symbolvalue.Round;
}
else
{
LogTextBox.AppendText("\r\n" + "There is no BO Symbol for this id:" + symbol_id);
}
transacPrice = Math.Round(transacPrice*(double) MtyPrice, round);
qty = (double) (qty*MtyVolume);
double value = -Math.Round(qty*transacPrice, round);
db.CpPositions.Add(new CpPosition
{
ReportDate = reportdate,
Brocker = "ABN",
TradeDate = tradedate,
Symbol = symbol,
Qty = qty,
Price = transacPrice,
BOSymbol = BoSymbol,
ValueDate = valuedate,
cp_id = getCPid(row.Substring(54, 6).Trim(), cpdic),
Type = type,
OptionType = optiontype,
Strike = strike,
Value = value,
ccy = ccy,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******"
});
}
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e2)
{
foreach (DbEntityValidationResult eve in e2.EntityValidationErrors)
{
Console.WriteLine(
"Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (DbValidationError ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
ve.PropertyName, ve.ErrorMessage);
}
}
throw;
}
return reportdate;
}
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.
}
private DateTime getcashmovements(List<string> rowlist)
{
var dbentity = new EXANTE_Entities(_currentConnection);
var listforDb = new List<FT>();
DateTime reportdate = DateTime.ParseExact(rowlist[0].Substring(6, 8), "yyyyMMdd",
CultureInfo.InvariantCulture);
Dictionary<string, Map> bomap = getMap("ABN");
Map symbolvalue;
foreach (string row in rowlist)
{
string symbol = row.Substring(62, 6).Trim();
string symbol2 = row.Substring(106, 4).Trim();
/* if (Convert.ToInt64(row.Substring(135, 9).Trim()) == 587856)
{
var t = 1;
}*/
string type = row.Substring(60, 2).Trim();
string bosymbol = "";
if (bomap.TryGetValue(symbol + type, out symbolvalue))
{
bosymbol = symbolvalue.BOSymbol;
}
dbentity.FT.Add(new FT
{
cp = row.Substring(54, 6).TrimEnd(),
brocker = "ABN",
ReportDate =
DateTime.ParseExact(row.Substring(6, 8), "yyyyMMdd",
CultureInfo.InvariantCulture),
account_id = null,
timestamp = DateTime.Now,
symbol = symbol,
ccy = row.Substring(68, 3).Trim(),
value = row[105] != 'C'
? -1*Convert.ToDouble(row.Substring(87, 18))/100
: Convert.ToDouble(row.Substring(87, 18))/100,
valid = 1,
Type = type,
User = "******",
Comment = row.Substring(111, 24).TrimEnd(' '),
Reference = Convert.ToInt64(row.Substring(135, 9).Trim()),
ValueDate =
DateTime.ParseExact(row.Substring(79, 8), "yyyyMMdd",
CultureInfo.InvariantCulture),
TradeDate =
DateTime.ParseExact(row.Substring(71, 8), "yyyyMMdd",
CultureInfo.InvariantCulture),
BOSymbol = bosymbol,
GrossPositionIndicator = row.Substring(110, 1),
JOURNALACCOUNTCODE = row.Substring(106, 4),
ValueCCY = null,
counterccy = null
});
}
dbentity.SaveChanges();
return reportdate;
}
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 DateTime LekTradeUploading()
{
DialogResult result = openFileDialog2.ShowDialog();
// var idSymbol = 7;
int idMacside = 5;
int idAccount = 1;
// var idcurrency = 10;
// var idTradeDate = 2;
// var idqty = 6;
int idcp = 8;
// var idprice = 9;
int idTypeofTrade = 8;
int iddeliverydate = 4;
int idvalue = 11;
// var idexchfees = 12;
// var idfees = 13;
int idoftrade = 0;
Dictionary<string, Map> symbolmap = getMapping("Lek");
// var idTypeofOption = 9;
// var idstrike = 20;
if (result == DialogResult.OK) // Test result.
{
var db = new EXANTE_Entities(_currentConnection);
ColumnMapping cMapping = (from ct in db.ColumnMappings
where ct.Brocker == "LEK" && ct.FileType == "CSV"
select ct).FirstOrDefault();
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);
var allfromfile = new List<CpTrade>();
string lineFromFile = reader.ReadLine();
// Map symbolvalue;
var reportdate = new DateTime();
if (lineFromFile != null)
{
string[] rowstring = lineFromFile.Replace("\"", "").Split(Delimiter);
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);
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
double? MtyVolume = 1;
double? MtyPrice = 1;
string BoSymbol = null;
double? Leverage = 1;
// int round = 10;
if (lineFromFile == null) continue;
rowstring = lineFromFile.Replace("\"", "").Split(CSVDelimeter);
DateTime valuedate;
int side = -1;
double price = 0;
string symbol_id = rowstring[(int) cMapping.cSymbol].TrimEnd();
string typeoftrade = rowstring[idTypeofTrade].TrimEnd();
string typeofInstrument = "ST";
valuedate = DateTime.ParseExact(rowstring[iddeliverydate], cMapping.DateFormat,
CultureInfo.CurrentCulture);
BoSymbol = GetSymbolLek(symbolmap, symbol_id, ref MtyVolume, contractdetails, ref MtyPrice,
ref valuedate, ref Leverage);
price =
(double)
(double.Parse(rowstring[(int) cMapping.cPrice], CultureInfo.InvariantCulture)*MtyPrice);
if ((rowstring[idMacside] == "B") || (rowstring[idMacside] == "BOT"))
{
side = 1;
}
reportdate = DateTime.ParseExact(rowstring[(int) cMapping.cReportDate], cMapping.DateFormat,
CultureInfo.CurrentCulture);
string account_id = rowstring[idAccount].TrimEnd();
string ccy = rowstring[(int) cMapping.cCcy].TrimEnd();
DateTime TradeDate = DateTime.ParseExact(rowstring[(int) cMapping.cTradeDate],
cMapping.DateFormat,
CultureInfo.CurrentCulture);
double? qty = rowstring[(int) cMapping.cQty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[(int) cMapping.cQty])*side*MtyVolume
: double.Parse(rowstring[(int) cMapping.cQty], CultureInfo.InvariantCulture)*
side*
MtyVolume;
int? cp_id = getCPid(rowstring[idcp].Trim(), cpdic);
double exchFees = double.Parse(rowstring[(int) cMapping.cExchangeFees],
CultureInfo.InvariantCulture);
double value =
Math.Round(
-side*double.Parse(rowstring[(int) cMapping.cValue], CultureInfo.InvariantCulture), 2,
MidpointRounding.AwayFromZero);
double Fees = double.Parse(rowstring[(int) cMapping.cFee], CultureInfo.InvariantCulture);
string exchangeOrderId = rowstring[idoftrade].TrimEnd();
if (symbol_id.Contains("PUT") || symbol_id.Contains("CALL"))
{
typeofInstrument = "OP";
}
allfromfile.Add(new CpTrade
{
ReportDate = reportdate,
TradeDate = TradeDate,
BrokerId = "LEK",
Symbol = symbol_id,
Qty = qty,
Price = price,
ValueDate = valuedate,
cp_id = cp_id,
ExchangeFees = exchFees,
Fee = Fees,
Id = "",
TypeOfTrade = typeoftrade,
Type = typeofInstrument,
BOSymbol = BoSymbol,
BOTradeNumber = null,
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = exchangeOrderId,
Comment = account_id,
ExchFeeCcy = ccy,
ClearingFeeCcy = ccy,
ccy = ccy
});
}
}
TradesParserStatus.Text = "DB updating";
int i = 0;
foreach (CpTrade tradeIndex in allfromfile)
{
db.CpTrades.Add(tradeIndex);
i++;
}
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);
}
}
}
LogTextBox.AppendText("\r\n" + "Lek: " + i + " trades have been added");
return reportdate;
}
else return new DateTime(2011, 01, 01);
}
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 MacRecon(DateTime reportdate, List<CpTrade> trades)
{
var cplist = new List<string> {"CQG", "PATS"};
Dictionary<string, List<Ctrade>> boTradeslist =
CreateIdForBoTrades(getTradesFromDB(reportdate, cplist, true, null));
Array cpmapping = getBOtoABNMapping();
Dictionary<string, Map> bomap = getMap("Mac");
List<CpTrade> TradeList = CreateIdForCpTrades(getOnlyTrades(trades), "Mac");
var recon = new List<Reconcilation>();
var db = new EXANTE_Entities(_currentConnection);
foreach (CpTrade cpTrade in TradeList)
{
List<Ctrade> ctrade;
if (boTradeslist.TryGetValue(cpTrade.Id, out ctrade))
{
UpdateRecTrades(cpTrade, ctrade, db, recon);
ctrade.RemoveAt(0);
if (ctrade.Count == 0)
{
boTradeslist.Remove(cpTrade.Id);
}
}
else
{
}
}
db.SaveChanges();
foreach (Reconcilation reconcilation in recon)
{
db.Reconcilations.Add(reconcilation);
}
db.SaveChanges();
}
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 DateTime MacTradeUploading()
{
DialogResult result = openFileDialog2.ShowDialog();
int idSymbol = 6;
int idMacside = 11;
int idReportDate = 0;
int idAccount = 1;
int idcurrency = 4;
int idTradeDate = 10;
int idqty = 12;
int idcp = 19;
int idSellprice = 15;
int idBuyPrice = 13;
int idTypeofTrade = 8;
int iddeliverydate = 7;
int idcat = 5;
int idexchfees = 24;
int idfees = 23;
int idoftrade = 32;
Dictionary<string, Map> symbolmap = getMapping("Mac");
int idTypeofOption = 9;
int idstrike = 20;
int idvalue = 39;
if (result == DialogResult.OK) // Test result.
{
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);
var allfromfile = new List<CpTrade>();
string lineFromFile = reader.ReadLine();
Map symbolvalue;
var reportdate = new DateTime();
if (lineFromFile != null)
{
string[] rowstring = lineFromFile.Replace("\"", "").Split(Delimiter);
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);
while (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
double? MtyVolume = 1;
double? MtyPrice = 1;
string BoSymbol = null;
double? Leverage = 1;
int round = 10;
double? mtystrike = 1;
if (lineFromFile == null) continue;
rowstring = lineFromFile.Replace("\"", "").Split(CSVDelimeter);
DateTime? valuedate = null;
Contract mapContract;
int side = 1;
double price = 0;
string symbol_id = rowstring[idSymbol].TrimEnd();
string key = symbol_id;
string typeoftrade = rowstring[idTypeofTrade].TrimEnd();
if (typeoftrade == "O")
{
key = key + "OP";
}
DateTime deliveryDate = DateTime.ParseExact(rowstring[iddeliverydate], "yyMM",
CultureInfo.CurrentCulture);
if (symbolmap.TryGetValue(key, out symbolvalue))
{
MtyVolume = symbolvalue.MtyVolume;
MtyPrice = symbolvalue.MtyPrice;
BoSymbol = symbolvalue.BOSymbol;
round = (int) symbolvalue.Round;
mtystrike = symbolvalue.MtyStrike;
key = BoSymbol + ".";
BoSymbol = key + getLetterOfMonth(deliveryDate.Month) + deliveryDate.Year;
if (typeoftrade == "O")
{
key = BoSymbol + "." + rowstring[idTypeofOption].Trim() +
(double.Parse(rowstring[idstrike], CultureInfo.InvariantCulture)*mtystrike)
.ToString().Replace(".", "_");
BoSymbol = key;
}
if (contractdetails.TryGetValue(BoSymbol, out mapContract))
{
valuedate = mapContract.ValueDate;
Leverage = mapContract.Leverage;
}
else
{
valuedate = deliveryDate;
LogTextBox.AppendText("\r\n" + "Mac: No Map in Contracts for " + key);
if (typeoftrade == "O")
{
BoSymbol = key;
}
}
}
else
{
LogTextBox.AppendText("\r\n" + "Mac: No Map in Mapping table for " + symbol_id);
valuedate = deliveryDate;
}
if (rowstring[idMacside] == "S")
{
side = -1;
price =
(double) (double.Parse(rowstring[idSellprice], CultureInfo.InvariantCulture)*MtyPrice);
}
else
{
price =
(double) (double.Parse(rowstring[idBuyPrice], CultureInfo.InvariantCulture)*MtyPrice);
}
reportdate = Convert.ToDateTime(rowstring[idReportDate]);
string account_id = rowstring[idAccount].TrimEnd();
string ccy = rowstring[idcurrency].TrimEnd();
DateTime TradeDate = Convert.ToDateTime(rowstring[idTradeDate]);
double? qty = rowstring[idqty].IndexOf(".") == -1
? Convert.ToInt64(rowstring[idqty])*side*MtyVolume
: double.Parse(rowstring[idqty], CultureInfo.InvariantCulture)*side*MtyVolume;
int? cp_id = getCPid(rowstring[idcp].Trim(), cpdic);
string category = rowstring[idcat];
double value = double.Parse(rowstring[idvalue], CultureInfo.InvariantCulture);
double exchFees = double.Parse(rowstring[idexchfees], CultureInfo.InvariantCulture);
double Fees = double.Parse(rowstring[idfees], CultureInfo.InvariantCulture);
string exchangeOrderId = rowstring[idoftrade].TrimEnd();
allfromfile.Add(new CpTrade
{
ReportDate = reportdate,
TradeDate = TradeDate,
BrokerId = "Mac",
Symbol = symbol_id,
Type = typeoftrade,
Qty = qty,
Price = price,
ValueDate = valuedate,
cp_id = cp_id,
ExchangeFees = exchFees,
Fee = Fees,
Id = null,
BOSymbol = BoSymbol,
BOTradeNumber = null,
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
// FullId = null,
BOcp = null,
exchangeOrderId = exchangeOrderId,
TypeOfTrade = category,
Comment = account_id,
ExchFeeCcy = ccy,
ClearingFeeCcy = ccy,
ccy = ccy
});
}
}
TradesParserStatus.Text = "DB updating";
foreach (CpTrade tradeIndex in allfromfile)
{
db.CpTrades.Add(tradeIndex);
}
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);
}
}
}
return reportdate;
}
else return new DateTime(2011, 01, 01);
}
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 void AbnRecon(DateTime reportdate, List<CpTrade> trades)
{
var cplist = new List<string> {"LEK", "CQG", "FASTMATCH", "CURRENEX","EXANTE", ""};
var mltytrades = MultyTradesCheckBox.Checked;
var skipspr = SkipspreadcheckBox.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))
{
UpdateRecTrades(cpTrade, ctrade, db, boTradeslist, recon);
ctrade.RemoveAt(0);
if (ctrade.Count == 0)
{
boTradeslist.Remove(cpTrade.Id);
}
}
else
{
if (mltytrades)
{
var reclist = CheckMultitrades(cpTrade, boTradeslist.Values.SelectMany(x => x).ToList());
if (reclist != null)
{
var n = reclist.Count;
for (var i = 0; i < n; i++)
{
var keysWithMatchingValues =
boTradeslist.Where(p => p.Value[0].fullid == reclist[0].fullid)
.Select(p => p.Key)
.FirstOrDefault();
UpdateRecTrades(cpTrade, reclist, db, boTradeslist, recon);
reclist.RemoveAt(0);
if (boTradeslist[keysWithMatchingValues].Count == 1)
{
boTradeslist.Remove(keysWithMatchingValues);
}
else
{
boTradeslist[keysWithMatchingValues].RemoveAt(0);
}
}
}
}
}
}
db.SaveChanges();
if (mltytrades)
{
for (int j = boTradeslist.Count - 1; j >= 0; j--)
{
var currentkey = boTradeslist.Keys.ElementAt(j);
List<Ctrade> valuePair = boTradeslist[currentkey];
for (var listindex = 0; listindex < valuePair.Count; listindex++)
{
var ctrade = valuePair[listindex];
if (ctrade.symbol_id.Contains(".CS/"))
{
}
var reclist = CheckMultitradesBack(ctrade,abnTradeslist.Where(x => (x.BOTradeNumber == null)).ToList());
if (reclist != null)
{
var n = reclist.Count;
for (var i = 0; i < n; i++)
{
var templist = new List<Ctrade> {ctrade};
UpdateRecTrades(reclist[i], templist, db, boTradeslist, recon);
}
db.SaveChanges();
boTradeslist[currentkey].RemoveAt(listindex);
}
}
if (valuePair.Count == 0)
{
boTradeslist.Remove(currentkey);
}
}
}
/* 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();
}
