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();
}
// public int Method(Bar bar, string propertyName)
// var prop = typeof(Bar).GetProperty(propertyName);
// int value = (int)prop.GetPage(bar,null);
private void button3_Click(object sender, EventArgs e)
{
DateTime reportdate = ABNDate.Value; //todo Get report date from xml Processing date
TradesParserStatus.Text = "Processing";
if (!noparsingCheckbox.Checked)
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start ADSS trades uploading");
reportdate = Adssparsing();
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "ADSS trades uploading completed." +
(TimeEnd - TimeStart).ToString());
}
RecProcess(reportdate, "ADSS");
var db = new EXANTE_Entities(_currentConnection);
// db.Database.ExecuteSqlCommand("UPDATE CpTrades Set value = -Qty*Price WHERE BrokerId LIKE '%adss%'");
db.Dispose();
TradesParserStatus.Text = "Done";
Console.WriteLine(""); // <-- For debugging use. */
/* var nextdate = reportDate;
var startdate = new DateTime(minDate.Year, minDate.Month, minDate.Day, 0, 0, 0);
var queryable =
from ct in db.Ctrades
where ct.Date >= startdate && ct.cp_id == "ADSS"
select new { ct.ExchangeOrderId, 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(_currentConnection))
{
data.Database.ExecuteSqlCommand(
"UPDATE Ctrades Set RecStatus ={0} WHERE fullid = {1}", true,
botradeItemlist.ctradeid);
}
}
}
}
foreach (CpTrade tradeIndex in allfromfile)
{
db.CpTrades.Add(tradeIndex);
}
db.SaveChanges();
foreach (Reconcilation reconitem in recon)
{
reconitem.CpTrade_id = allfromfile[(int)reconitem.CpTrade_id].FullId;
db.Reconcilations.Add(reconitem);
}
db.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 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 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 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 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<CpTrade> OpenConverting(List<InitialTrade> lInitTrades, string cp)
{
DateTime TimeStartConvert = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStartConvert.ToLongTimeString() + ": " + "start " + cp +
" trades Converting");
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, Map> symbolmap = getMapping(cp);
Dictionary<string, string> typemap =
(from ct in db.Mappings
where ct.valid == 1 && ct.Brocker == cp && ct.Type == "Type"
select ct).ToDictionary(k => k.BrockerSymbol, k => k.BOSymbol);
var lCpTrade = new List<CpTrade>();
foreach (InitialTrade initTrade in lInitTrades)
{
string type = initTrade.Type;
if (typemap.ContainsKey(initTrade.Type)) type = typemap[initTrade.Type];
if (initTrade.Comment != null && initTrade.Comment.Contains("REPO")) type = "REPO";
double? Price = initTrade.Price;
double? Qty = initTrade.Qty;
double? value = initTrade.value;
DateTime? ValueDate = initTrade.ValueDate;
String BOSymbol = null;
if (symbolmap.ContainsKey(initTrade.Symbol + type))
{
Map map = symbolmap[initTrade.Symbol + type];
BOSymbol = map.BOSymbol;
Price = Price*map.MtyPrice;
Qty = Qty*map.MtyVolume;
value = value*map.Leverage;
if (type != "FX") ValueDate = map.ValueDate;
type = map.Type;
}
if ((Qty > 0) && (value != null)) value = -Math.Abs((double) value);
double? fee = null;
if (initTrade.Fee != null) fee = -Math.Abs((double) initTrade.Fee);
lCpTrade.Add(new CpTrade
{
ReportDate = initTrade.ReportDate,
TradeDate = initTrade.TradeDate,
BrokerId = initTrade.BrokerId,
Symbol = initTrade.Symbol,
Type = type,
Qty = Qty,
Price = Price,
ValueDate = ValueDate,
cp_id = initTrade.cp_id,
ExchangeFees = initTrade.ExchangeFees,
Fee = fee,
BOSymbol = BOSymbol,
//?BOTradeNumber =
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
//?BOcp =
exchangeOrderId = initTrade.exchangeOrderId,
// TypeOfTrade = initTrade.Comment.Contains("REPO")?"REPO": initTrade.TypeOfTrade,
TypeOfTrade = initTrade.TypeOfTrade,
Comment = initTrade.Comment,
ExchFeeCcy = initTrade.ExchFeeCcy,
ClearingFeeCcy = initTrade.ClearingFeeCcy,
ccy = initTrade.ccy,
Fee2 = initTrade.Fee2,
Fee3 = initTrade.Fee3,
Interest = initTrade.AccruedInterest,
account = initTrade.Account
}
);
}
db.Dispose();
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + cp + " trades converting completed." +
(TimeEnd - TimeStartConvert).ToString());
return lCpTrade;
}
private void DEXParsing(object sender, EventArgs e)
{
DialogResult result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
var db = new EXANTE_Entities(_currentConnection);
var reader = new StreamReader(openFileDialog2.FileName);
var allfromfile = new List<CpTrade>();
string lineFromFile = reader.ReadLine();
if (lineFromFile != null)
{
while (!reader.EndOfStream &&
!lineFromFile.Contains("F U T U R E S / O P T I O N S C O N F I R M A T I O N S"))
{
lineFromFile = reader.ReadLine();
}
if (!reader.EndOfStream)
{
lineFromFile = reader.ReadLine();
lineFromFile = reader.ReadLine();
if (lineFromFile.Contains("The following option positions have expired."))
lineFromFile = reader.ReadLine();
while (!reader.EndOfStream && !lineFromFile.Contains("Recap Of Confirm Activity") &&
!lineFromFile.Contains("Total Value in Base Currency") &&
!lineFromFile.Contains("F U T U R E S / O P T I O N S O P E N P O S I T I O N S"))
{
DateTime tradedate = DateTime.ParseExact(lineFromFile.Substring(0, 8).Replace(" ", "0"),
"dd/MM/yy", CultureInfo.CurrentCulture);
double qty = OSLExtractQty(lineFromFile);
string symbol = lineFromFile.Substring(33, 32).TrimStart().TrimEnd();
string OptionType = lineFromFile.Substring(55, 1).Trim();
string OptionStrike = lineFromFile.Substring(57, 9).Trim();
string ccy = lineFromFile.Substring(94, 3);
double price = Convert.ToDouble(lineFromFile.Substring(72, 6).Trim());
DateTime valuedate = DateTime.ParseExact(lineFromFile.Substring(33, 5), "MMMyy",
CultureInfo.CurrentCulture);
string ExchFeeCcy = "";
double ExchangeFees = 0;
string ClearingFeeCcy = "";
double Fee = 0;
lineFromFile = reader.ReadLine();
string vt = lineFromFile.Substring(2, 1);
while (!reader.EndOfStream && !lineFromFile.Contains("COMMISSION") &&
!lineFromFile.Contains("TOTAL FEES") && lineFromFile.Substring(2, 1) != "/" &&
!lineFromFile.Contains(
"F U T U R E S / O P T I O N S O P E N P O S I T I O N S"))
{
lineFromFile = reader.ReadLine();
}
if (lineFromFile.Contains("COMMISSION"))
{
ExchFeeCcy = lineFromFile.Substring(94, 3).Trim();
ExchangeFees = -Convert.ToDouble(lineFromFile.Substring(103, 12).Trim());
}
lineFromFile = reader.ReadLine();
while (!reader.EndOfStream && !lineFromFile.Contains("COMMISSION") &&
!lineFromFile.Contains("TOTAL FEES") && lineFromFile.Substring(2, 1) != "/" &&
!lineFromFile.Contains(
"F U T U R E S / O P T I O N S O P E N P O S I T I O N S"))
{
lineFromFile = reader.ReadLine();
}
if (lineFromFile.Contains("TOTAL FEES"))
{
ClearingFeeCcy = lineFromFile.Substring(94, 3).Trim();
Fee = -Convert.ToDouble(lineFromFile.Substring(103, 12).Trim());
}
allfromfile.Add(new CpTrade
{
ReportDate = ABNDate.Value.Date,
account = "DEX2565",
BrokerId = "OPEN",
Symbol = symbol,
Qty = qty,
Price = price,
ccy = ccy,
ValueDate = valuedate,
TradeDate = tradedate,
Type = (OptionType == "") ? "FU" : "OP",
ExchFeeCcy = ExchFeeCcy,
ExchangeFees = ExchangeFees,
ClearingFeeCcy = ClearingFeeCcy,
Fee = Fee,
Timestamp = DateTime.Now,
valid = 1,
username = "******"
});
if (lineFromFile.Substring(2, 1) != "/" &&
!lineFromFile.Contains("F U T U R E S / O P T I O N S O P E N P O S I T I O N S"))
{
lineFromFile = reader.ReadLine();
}
}
}
foreach (CpTrade cpTrade in allfromfile)
{
db.CpTrades.Add(cpTrade);
}
SaveDBChanges(ref db);
db.Dispose();
}
}
}
private void cpCostToolStripMenuItem_Click(object sender, EventArgs e)
{
var db = new EXANTE_Entities(_currentConnection);
/*var ctradeslist = (from r in db.Ctrades
where r.BOtradeTimestamp.ToString().Contains("2015-12") && r.valid == 1
select r).ToList();*/
List<cpCost_cTrade> ctradeslist = (from r in db.Ctrades
where r.BOtradeTimestamp.ToString().Contains("2016-08") && r.valid == 1
select new cpCost_cTrade
{
symbol_id = r.symbol_id,
cp_id = r.cp_id,
account_id = r.account_id,
fees = r.fees,
currency = r.currency,
qty = r.qty,
tradeNumber = r.tradeNumber
}).ToList();
var dCpCost = new Dictionary<string, CpCost>();
int i = 0;
List<cpCost_cpTrade> allcptrades = (from cp in db.CpTrades
where
cp.TradeDate.ToString().Contains("2016-08") && cp.valid == 1 &&
cp.BOTradeNumber != null
select new cpCost_cpTrade
{
Symbol = cp.Symbol,
BrokerId = cp.BrokerId,
ccy = cp.ccy,
ExchFeeCcy = cp.ExchFeeCcy,
ExchangeFees = cp.ExchangeFees,
Fee = cp.Fee,
Fee2 = cp.Fee2,
Fee3 = cp.Fee3,
Qty = cp.Qty,
BOTradeNumber = cp.BOTradeNumber
}).ToList();
int n = ctradeslist.Count;
foreach (cpCost_cTrade ctrade in ctradeslist)
{
i++;
string trnumber = ctrade.tradeNumber.ToString();
/* if (trnumber == "30123135")
{
var t = 1;
}*/
IEnumerable<cpCost_cpTrade> cptrades = allcptrades.Where(cp => cp.BOTradeNumber.Contains(trnumber));
//.ToList();
List<cpCost_cpTrade> listcptrades = cptrades.ToList();
/*(from cp in allcptrades
where cp.BOTradeNumber.Contains(ctrade.tradeNumber.ToString())
select cp).ToList();
/* allcptrades.Where()
(from cp in db.CpTrades
where cp.TradeDate.ToString().Contains("2015-12") && cp.BOTradeNumber.Contains(ctrade.tradeNumber.ToString()) && cp.valid == 1
select cp).ToList();*/
double ExchFee = 0, cpFee = 0, sumQty = 0;
cpCost_cpTrade item = null;
if (listcptrades.Count > 0)
{
foreach (cpCost_cpTrade trade in listcptrades)
{
if (trade.ExchangeFees != null)
ExchFee = Math.Abs(ExchFee) + Math.Abs((double) trade.ExchangeFees);
if (trade.Fee != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee);
if (trade.Fee2 != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee2);
if (trade.Fee3 != null) cpFee = Math.Abs(cpFee) + Math.Abs((double) trade.Fee3);
sumQty = sumQty + Math.Abs((double) trade.Qty);
}
if (sumQty != 0)
{
ExchFee = -(ExchFee*Math.Abs((double) ctrade.qty)/sumQty);
cpFee = -(cpFee*Math.Abs((double) ctrade.qty)/sumQty);
}
else
{
ExchFee = -(ExchFee);
cpFee = -(cpFee);
}
item = listcptrades[0];
}
string id = ctrade.account_id + ctrade.symbol_id + ctrade.cp_id;
CpCost ElementCpcost;
if (dCpCost.TryGetValue(id, out ElementCpcost))
{
ElementCpcost.BOFee = Math.Round((double) (ElementCpcost.BOFee + Math.Abs((double) ctrade.fees)), 2);
ElementCpcost.CpFee = Math.Round((double) (ElementCpcost.CpFee + cpFee), 2);
ElementCpcost.ExchFee = Math.Round((double) (ElementCpcost.ExchFee + ExchFee), 2);
ElementCpcost.SumQty = ElementCpcost.SumQty + sumQty;
ElementCpcost.NumberOfTrades = ElementCpcost.NumberOfTrades + listcptrades.Count();
if ((item != null) && (ElementCpcost.CPsymbol != null))
{
ElementCpcost.CP = item.BrokerId;
ElementCpcost.CpClearingCCY = item.ccy;
ElementCpcost.CpExchCcy = item.ExchFeeCcy;
ElementCpcost.CPsymbol = item.Symbol;
ElementCpcost.NumberOfTrades = listcptrades.Count();
}
}
else
{
dCpCost.Add(id, new CpCost
{
Date = new DateTime(2016, 01, 1),
account = ctrade.account_id,
BOCcy = ctrade.currency,
BOCp = ctrade.cp_id,
BOFee = Math.Round(Math.Abs((double) ctrade.fees), 2),
BOsymbol = ctrade.symbol_id,
CP = item == null ? null : item.BrokerId,
CpClearingCCY = item == null ? null : item.ccy,
CpExchCcy = item == null ? null : item.ExchFeeCcy,
CpFee = Math.Round(cpFee, 2),
ExchFee = Math.Round(ExchFee, 2),
CPsymbol = item == null ? null : item.Symbol,
SumQty = sumQty,
NumberOfTrades = item == null ? 0 : listcptrades.Count()
});
}
}
foreach (var pair in dCpCost)
{
db.CpCost.Add(pair.Value);
SaveDBChanges(ref db);
}
// SaveDBChanges(ref db);
db.Dispose();
}
private void button8_Click_1(object sender, EventArgs e)
{
FORTSReconciliation("ITInvest", null);
var db = new EXANTE_Entities(_currentConnection);
db.Database.ExecuteSqlCommand(
"UPDATE CpTrades AS cp INNER JOIN Contracts AS c ON c.id = cp.BOSymbol SET cp.value = - cp.Qty*cp.Price*c.Leverage WHERE cp.BrokerId LIKE '%ITInvest' AND ReportDate > '2016-06-01'");
db.Dispose();
}
private void button6_Click(object sender, EventArgs e)
{
TradesParserStatus.Text = "Processing";
updateFORTSccyrates();
calcualteVM(ABNDate.Value, "M&L");
calcualteVM(ABNDate.Value, "MOEX");
calcualteVM(ABNDate.Value, "EXANTE");
calcualteVM(ABNDate.Value, "MOEX-SPECTRA");
calcualteVM(ABNDate.Value, "OPEN");
calcualteVM(ABNDate.Value, "INSTANT");
var db = new EXANTE_Entities(_currentConnection);
db.Database.ExecuteSqlCommand(
"UPDATE FT Set Account_id = {0} WHERE Account_id LIKE {1} AND ReportDate = {2}", "UJL5180.INV",
"UJL5180.001%", ABNDate.Value.Date);
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 static string GetAccountIdFromTradeNumber(int? tradenumber)
{
var db = new EXANTE_Entities(_currentConnection);
string accountnumber = (from ctrade in db.Ctrades
where ctrade.valid == 1 && ctrade.tradeNumber == tradenumber
select ctrade.account_id).ToList()[0];
db.Dispose();
return accountnumber;
}
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 double? GetVM(DateTime vmDate, string brocker)
{
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = vmDate.AddDays(1);
double? sum =
db.FT.Where(
o =>
(o.ReportDate >= vmDate.Date && o.ReportDate < nextdate.Date && o.valid == 1 && o.brocker == brocker))
.Sum(o => o.value);
db.Dispose();
return sum;
}
private List<CpTrade> InitTradesConverting(List<InitialTrade> lInitTrades, string cp, bool checkIdflag = false,
string checkIdCp = "")
{
DateTime TimeStartConvert = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStartConvert.ToLongTimeString() + ": " + "start " + cp +
" trades Converting");
var db = new EXANTE_Entities(_currentConnection);
Dictionary<string, Map> symbolmap = getMapping(cp);
var lCpTrade = new List<CpTrade>();
Dictionary<string, long> checkId = null;
if (checkIdflag)
{
checkId = (from ct in db.CpTrades
where ct.TradeDate.ToString().Contains("2016-0") && ct.BrokerId == checkIdCp
select ct).ToDictionary(k => k.exchangeOrderId, k => k.FullId);
}
foreach (InitialTrade initTrade in lInitTrades)
{
string type = "FU";
if (initTrade.Type == "O") type = "OP";
double? Price = initTrade.Price;
double? Qty = initTrade.Qty;
double? value = initTrade.value;
DateTime? ValueDate = initTrade.ValueDate;
if (ValueDate == null) ValueDate = new DateTime(2011, 01, 01);
String BOSymbol = null;
string key = initTrade.Symbol + type; // +ValueDate.Value.ToShortDateString();
if (symbolmap.ContainsKey(key))
{
Map map = symbolmap[key];
BOSymbol = map.BOSymbol;
Price = Price*map.MtyPrice;
Qty = Qty*map.MtyVolume;
value = value*map.Leverage;
if (type == "OP")
{
BOSymbol = BOSymbol + ".";
if (map.UseDayInTicker == true)
{
BOSymbol = BOSymbol + initTrade.ValueDate.Value.Day.ToString();
}
if (map.MtyStrike == null) map.MtyStrike = 1;
BOSymbol = BOSymbol + getLetterOfMonth(initTrade.ValueDate.Value.Month) +
initTrade.ValueDate.Value.Year + "." + initTrade.OptionType +
(initTrade.Strike*map.MtyStrike).ToString();
}
else
{
if (map.calendar == 1)
{
BOSymbol = BOSymbol + "." + getLetterOfMonth(initTrade.ValueDate.Value.Month) +
initTrade.ValueDate.Value.Year;
}
}
}
if (checkIdflag)
{
if (!checkId.ContainsKey(initTrade.exchangeOrderId))
{
lCpTrade.Add(new CpTrade
{
ReportDate = initTrade.ReportDate,
TradeDate = initTrade.TradeDate,
BrokerId = initTrade.BrokerId,
Symbol = initTrade.Symbol,
Type = type,
Qty = Qty,
Price = Price,
ValueDate = ValueDate,
cp_id = initTrade.cp_id,
ExchangeFees = initTrade.ExchangeFees,
Fee = initTrade.Fee,
BOSymbol = BOSymbol,
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
exchangeOrderId = initTrade.exchangeOrderId,
TypeOfTrade = initTrade.TypeOfTrade,
Comment = initTrade.Comment,
ExchFeeCcy = initTrade.ExchFeeCcy,
ClearingFeeCcy = initTrade.ClearingFeeCcy,
ccy = initTrade.ccy,
account = initTrade.Account,
TradeId = initTrade.TradeId
});
}
}
else
{
lCpTrade.Add(new CpTrade
{
ReportDate = initTrade.ReportDate,
TradeDate = initTrade.TradeDate,
BrokerId = initTrade.BrokerId,
Symbol = initTrade.Symbol,
Type = type,
Qty = Qty,
Price = Price,
ValueDate = ValueDate,
cp_id = initTrade.cp_id,
ExchangeFees = initTrade.ExchangeFees,
Fee = initTrade.Fee,
BOSymbol = BOSymbol,
value = value,
Timestamp = DateTime.UtcNow,
valid = 1,
username = "******",
exchangeOrderId = initTrade.exchangeOrderId,
TypeOfTrade = initTrade.TypeOfTrade,
Comment = initTrade.Comment,
ExchFeeCcy = initTrade.ExchFeeCcy,
ClearingFeeCcy = initTrade.ClearingFeeCcy,
ccy = initTrade.ccy,
account = initTrade.Account,
TradeId = initTrade.TradeId
});
}
}
db.Dispose();
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + cp + " trades converting completed." +
(TimeEnd - TimeStartConvert).ToString());
return lCpTrade;
}
private static Dictionary<string, int> Getlistofccy_modified(string txt, ref List<string> ccy)
{
int lastindexofstar = txt.IndexOf('*');
var listofccy = new Dictionary<string, int>();
while (lastindexofstar > -1)
{
int endstar = txt.IndexOf("*", lastindexofstar + 1);
string cnt_ccy = txt.Substring(lastindexofstar + 1, endstar - lastindexofstar - 1).TrimStart().TrimEnd();
listofccy.Add(txt.Substring(lastindexofstar + 1, endstar - lastindexofstar - 1).TrimStart().TrimEnd(),
endstar + 1);
string match = ccy.FirstOrDefault(stringToCheck => stringToCheck.Contains(cnt_ccy));
if (match == null)
{
ccy.Add(cnt_ccy);
var db = new EXANTE_Entities(_currentConnection);
db.RJO_listccy.Add(new RJO_listccy {Ccy = cnt_ccy, valid = 1});
SaveDBChanges(ref db);
db.Dispose();
}
lastindexofstar = txt.IndexOf("*", endstar + 1);
}
return listofccy;
}
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 double getatomofVM(string symbol, DateTime VMDate)
{
var db = new EXANTE_Entities(_currentConnection);
double atomvalue = 0;
int indexofOption = CustomIndexOf(symbol, '.', 3);
string key = symbol;
if (indexofOption > 0)
{
key = symbol.Substring(0, indexofOption + 1);
}
List<Mapping> map =
(from ct in db.Mappings
where ct.valid == 1 && ct.Brocker == "OPEN" && ct.Type == "FORTS" && ct.BOSymbol == key
select ct).ToList();
if (map.Count == 1)
{
atomvalue = (double) (map[0].MtyPrice/map[0].MtyVolume);
if (map[0].Round == 1)
{
IQueryable<Price> ccyrateFromDblinq =
(from ct in db.Prices
where
ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") &&
ct.Date == VMDate.Date
select ct);
double ccyrateFromDb = 0;
if (!ccyrateFromDblinq.Any())
{
// updateFORTSccyrates(VMDate.ToString("dd.MM.yyyy"));
ccyrateFromDb =
(double) (from ct in db.Prices
where
ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker.Contains("USDRUB") &&
ct.Date == VMDate.Date
select ct).ToList()[0].Price1;
}
else
{
ccyrateFromDb = (double) ccyrateFromDblinq.ToList()[0].Price1;
}
atomvalue = Math.Round((atomvalue*ccyrateFromDb), 5, MidpointRounding.AwayFromZero);
}
}
db.Dispose();
return atomvalue;
}
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 List<FullTrade> Getlistofaccountposition(DateTime fortsDate, string Brocker)
{
var db = new EXANTE_Entities(_currentConnection);
DateTime nextdate = fortsDate.AddDays(1);
IQueryable<FullTrade> positionbefore =
from ct in db.Ctrades
where
ct.valid == 1 && ct.Date < fortsDate.Date && ct.symbol_id.Contains("FORTS") && ct.cp_id == Brocker
group ct by new
{
ct.account_id,
ct.symbol_id
}
into g
where g.Sum(x => x.qty) != 0
select
new FullTrade
{
Account = g.Key.account_id,
Symbol = g.Key.symbol_id,
Qty = (double) g.Sum(x => x.qty),
Price = 0,
Value = 0
};
// var listofTrades = positionbefore.ToList();
var listofTrades = new List<FullTrade>(positionbefore.ToList());
foreach (FullTrade listofTrade in listofTrades)
{
listofTrade.Price = GetFortsPrices(fortsDate, listofTrade.Symbol);
}
IQueryable<FullTrade> tradesToday =
from ct in db.Ctrades
where
ct.valid == 1 && ct.Date < nextdate.Date && ct.Date >= fortsDate.Date &&
ct.symbol_id.Contains("FORTS") && ct.cp_id == Brocker
select
new FullTrade
{
Account = ct.account_id,
Symbol = ct.symbol_id,
Qty = (double) ct.qty,
Price = (double) ct.price,
Value = 0
};
listofTrades.AddRange(tradesToday.ToList());
db.Dispose();
return listofTrades;
}
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 double GetPrice(DateTime VMDate, string symbol)
{
var db = new EXANTE_Entities(_currentConnection);
IQueryable<Price> pricelinq = from ct in db.Prices
where
ct.Valid == 1 && ct.Type == "FORTS" && ct.Ticker == symbol &&
ct.Date == VMDate.Date
select ct;
if (pricelinq.Any())
{
var returnvalue = (double) pricelinq.ToList()[0].Price1;
db.Dispose();
return returnvalue;
}
else
{
db.Dispose();
return UpdateFortsPrices(VMDate, symbol);
}
}
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 GetRowBalance()
{
DialogResult result = openFileDialog2.ShowDialog();
if (result == DialogResult.OK) // Test result.
{
DateTime TimeStart = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeStart.ToLongTimeString() + ": " + "start OPEN Balance uploading");
var db = new EXANTE_Entities(_currentConnection);
var ObjExcel = new Application();
//Открываем книгу.
Workbook ObjWorkBook = ObjExcel.Workbooks.Open(openFileDialog2.FileName,
0, false,
5, "", "",
false,
XlPlatform
.xlWindows,
"",
true, false, 0, true,
false, false);
//Выбираем таблицу(лист).
Worksheet ObjWorkSheet;
ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Portfolio evaluation"];
Range xlRange = ObjWorkSheet.UsedRange;
string account = xlRange.Cells[11, 8].value2;
if (account == null) account = xlRange.Cells[12, 7].value2;
dynamic ccy = xlRange.Cells[14, 8].value2;
if (ccy == null) ccy = xlRange.Cells[15, 7].value2;
ObjWorkSheet = (Worksheet) ObjWorkBook.Sheets["Cash flow"];
xlRange = ObjWorkSheet.UsedRange;
var reportdate = (DateTime) DateFromExcelCell(xlRange.Cells[3, 1].value2, "dd.MM.yyyy");
RemoveRecordFromRowBalance(db, reportdate, "Open", account);
GetCashFlowOSL(ObjWorkBook, db, account, reportdate);
GetPortfolioOSL(ObjWorkBook, db, reportdate, account, ccy);
GetOSLBalanceData("Gross amount of non-settled trades", ObjWorkBook, ref xlRange, db, ccy, reportdate,
account);
GetOSLBalanceData("Planned brokerage commission", ObjWorkBook, ref xlRange, db, ccy, reportdate, account);
GetOSLBalanceData("Other planned fees", ObjWorkBook, ref xlRange, db, ccy, reportdate, account);
PutNAVOSL(ObjWorkBook, ref xlRange, db, ccy, reportdate, account);
// ObjectParameter qty = new ObjectParameter("Name", typeof(Int16));
/* var idParam = new SqlParameter {ParameterName = "cp",Value = "OPEN"};
var CountParam = new SqlParameter { ParameterName = "number", Value = 0, Direction = ParameterDirection.Output };
mSqlCmdInsertCustomers.Parameters.Clear();
mSqlCmdInsertCustomers.Parameters.AddWithValue("param1", "value1");
mSqlCmdInsertCustomers.Parameters.AddWithValue("param2", "value2");
.
.
.
mSqlCmdInsertCustomers.Parameters.AddWithValue("paramN", "valueN");*/
//var t= db.
// var results = db.Database.SqlQuery<int>("exec CheckMappingBalance @cp, @number out", idParam, CountParam);
// db.call
// remove comments var results = db.Database.ExecuteSqlCommand("exec CheckMappingBalance @cp, @number out", idParam, CountParam);
//var person = results;
// remove comments var votes = (int)CountParam.Value;
/*
var date = new SqlParameter("@date", _msg.MDate);
var subject = new SqlParameter("@subject", _msg.MSubject);
var body = new SqlParameter("@body", _msg.MBody);
var fid = new SqlParameter("@fid", _msg.FID);
this.Database.ExecuteSqlCommand("exec messageinsert @Date , @Subject , @Body , @Fid", date, subject, body, fid);
*/
// db.Database.SqlQuery<int>("CheckMappingBalance", name).SingleOrDefault();
//to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset
//var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault();
//context.GetDepartmentName(, name);
// Console.WriteLine(name.Value);
SaveDBChanges(ref db);
db.Dispose();
ObjWorkBook.Close();
ObjExcel.Quit();
Marshal.FinalReleaseComObject(ObjWorkBook);
Marshal.FinalReleaseComObject(ObjExcel);
DateTime TimeEnd = DateTime.Now;
LogTextBox.AppendText("\r\n" + TimeEnd.ToLongTimeString() + ": " + "OPEN Balance Completed for+" +
reportdate.ToShortDateString() + openFileDialog2.FileName);
}
}
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 string GetSymbolRJO(Dictionary<string, Map> symbolmap, string symbol_id, ref double? MtyVolume,
Dictionary<string, Contract> contractdetails, ref double? MtyPrice,
ref DateTime valuedate, ref double? Leverage, ref string typeoftrade)
{
Map symbolvalue;
int round;
string BoSymbol = null;
string key = symbol_id;
string type = "";
string strike = "";
typeoftrade = "FU";
if (symbol_id.Contains("CALL") || symbol_id.Contains("PUT"))
{
type = symbol_id.Substring(0, symbol_id.IndexOf(" ")).Substring(0, 1);
key = key.Substring(symbol_id.IndexOf(" ") + 1);
typeoftrade = "OP";
}
int nextspace = key.IndexOf(" ");
string month = key.Substring(0, nextspace);
key = key.Substring(nextspace + 1);
nextspace = key.IndexOf(" ");
string year = "20" + key.Substring(0, nextspace);
key = key.Substring(nextspace + 1);
if (type != "")
{
strike = key.Substring(key.LastIndexOf(" ") + 1);
key = key.Substring(0, key.LastIndexOf(" ")).TrimEnd();
}
key = key + typeoftrade;
if (symbolmap.TryGetValue(key, out symbolvalue))
{
MtyVolume = symbolvalue.MtyVolume;
MtyPrice = symbolvalue.MtyPrice;
BoSymbol = symbolvalue.BOSymbol;
round = (int) symbolvalue.Round;
key = BoSymbol + "." + GetMonthLetter(month) + year;
if (type != "")
{
if (symbolvalue.MtyStrike != null)
strike =
(Math.Round((decimal) (Convert.ToInt32(strike)*symbolvalue.MtyStrike), 5)).ToString()
.Replace('.', '_');
key = key + "." + type + strike;
}
Contract mapContract;
if (contractdetails.TryGetValue(key, out mapContract))
{
int Digitmonth = GetMonthFromLetter(GetMonthLetter(month));
if (Digitmonth < 10) month = "0" + Digitmonth;
var db = new EXANTE_Entities(_currentConnection);
string t = "update Ctrades SET value_date= '" + year + "-" + month + "-01' where symbol_id='" + key +
"'";
// db.Database.ExecuteSqlCommand("CALL updatecontract('" + key + "','" + year + "-" + month + "-01')");
// db.Database.ExecuteSqlCommand("update Ctrades SET value_date= '" + year + "-" + month + "-01' where symbol_id='"+key+"'");
// db.Database.ExecuteSqlCommand("update Contracts SET ValueDate= '" + year + "-" + month + "-01' where id='" + key + "'");
db.Dispose();
// valuedate = (DateTime) mapContract.ValueDate;
Leverage = mapContract.Leverage;
BoSymbol = key;
}
else
{
LogTextBox.AppendText("\r\n" + "Lek: No Map in Contracts for " + key);
}
}
else
{
LogTextBox.AppendText("\r\n" + "Lek: No Map in Mapping table for " + symbol_id);
}
return BoSymbol;
}
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 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);
}
}
