private void btnSaveDBInfo_Click(object sender, EventArgs e)
{
int port = 0;
if (Logger.DebugMode)
{
Logger.LogTrace("Method: btnSaveDBInfo_Click");
}
DBConnectionDetails _tempObject = new DBConnectionDetails();
_tempObject.DBUsername = txtDBUsername.Text;
_tempObject.DBPassword = txtDBPassword.Text;
_tempObject.DBServer = txtDBServer.Text;
_tempObject.DBCatalog = txtDBCatalog.Text;
_tempObject.DBPort = int.TryParse(txtDBPort.Text, out port) ? port : -1;
_tempObject.DBType = (SupportedDatabases)Enum.Parse(typeof(SupportedDatabases), cmbDatabaseType.Text);
if (!DatabaseUtils.TestDBConnection(_tempObject, Logger))
{
DialogResult dialogResult = MessageBox.Show("Unable to connect to the database with selected info, would \n" +
"you like to save the connection details anyway?", "Connection Failed", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
ODL.Common.DatabaseUtils.Save(_tempObject, Logger);
}
}
else
{
ODL.Common.DatabaseUtils.Save(_tempObject, Logger);
}
}
public static IEnumerable <dynamic> GetPfsIds(DBConnectionDetails conn, IEnumerable <int> posIds)
{
string posStr = string.Join(",", posIds);
var query = string.Format("select posId, externalId from externalPositionMap where posId in ({0})", posStr);
return(DbUtils.ExecuteReader(conn, query, p => p, (r, i) => new { posId = r.GetInt32(0), pfsId = r.GetString(1) }, 30));
}
public static Dictionary <int, Dictionary <string, double> > ReadFXHist(DBConnectionDetails conn, int fromDate)
{
var query = string.Format(@"select eod_date, frgn_curr_code CCY, calc_bid fxrate from ARCH..CDM_currency_rates_arch where eod_date>={0}", fromDate);
var res = DbUtils.ExecuteReader(conn, query,
ri => new
{
EodDate = ri.GetOrdinal("eod_date"),
CCY = ri.GetOrdinal("CCY"),
Fx = ri.GetOrdinal("fxrate")
},
(r, ri) => new
{
EodDate = r.GetInt32(ri.EodDate),
CCY = r.GetString(ri.CCY),
Fx = r.GetDouble(ri.Fx)
},
30).ToList().GroupBy(du => du.EodDate).ToDictionary(du => du.Key, du => du.ToDictionary(d => d.CCY, d => d.Fx));
var lastDate = res.Keys.OrderByDescending(d => d).First();
var today = Utility.DateToRover8(DateTime.Today);
if (!res.ContainsKey(today))
{
res.Add(today, res[lastDate]);
}
return(res);
}
public static int GetPfsSecId(DBConnectionDetails conn, string pfsId)
{
if (pfsId == null)
{
return(-1);
}
return(DbUtils.ObtainCmdAndEval(conn.ConnectionString(), cmd => (int?)DbUtils.ExecuteScalar(false, cmd, "select secId from externalPositionMap where externalId = '" + pfsId + "'", null)) ?? -1);
}
private static IDatabaseProxy GetDatabaseProxyInstance(DBConnectionDetails dbDetails)
{
switch (dbDetails.DBType)
{
case "MongoDB": return(new MongoDBConnector(dbDetails));
case "LocalDB": return(LocalDB.Instance);
default: return(FindCustomDatabaseProxy(dbDetails));
}
}
private static IDatabaseProxy FindCustomDatabaseProxy(DBConnectionDetails dbDetails)
{
Type iDatabaseProxyType = typeof(IDatabaseProxy);
Assembly[] assemblies = AppDomain.CurrentDomain.GetAssemblies();
Type dbProxyType = assemblies
.SelectMany(s => s.GetTypes())
.SingleOrDefault(p => iDatabaseProxyType.IsAssignableFrom(p) && p.Name.Equals(dbDetails.DBType));
if (dbProxyType == null)
{
throw new ArgumentException($"Could not find db of type {dbDetails.DBType}");
}
return((IDatabaseProxy)Activator.CreateInstance(dbProxyType, dbDetails));
}
public MongoDBConnector(DBConnectionDetails connectionDetails)
{
ConnectionDetails = connectionDetails;
Settings = new MongoClientSettings()
{
ConnectionMode = ConnectionMode.Automatic,
Server = connectionDetails.Port == 0
? new MongoServerAddress(connectionDetails.Host)
: new MongoServerAddress(connectionDetails.Host, connectionDetails.Port)
};
if (!string.IsNullOrWhiteSpace(connectionDetails.Username))
{
Settings.Credential = MongoCredential.CreateCredential("admin",
connectionDetails.Username, connectionDetails.Password);
Settings.UseTls = true;
Settings.AllowInsecureTls = true;
}
}
static void Main(string[] args)
{
LogHandler LogObject = new LogHandler();
ODL.Common.DBConnectionDetails DbConnectionInf = new DBConnectionDetails()
{
DBServer = "",
DBCatalog = "",
DBUsername = "",
DBPassword = "",
DBType = SupportedDatabases.PostgreSQL,
DBPort = 0
};
String FileName = "C:\\Users\\Zach\\Downloads\\productDownload_2020-10-01T101650.zip";
//String FileName = "C:\\Users\\Zach\\Downloads\\NYSTATE_EVAL_2014.zip";
IngestBase x = new USCensusS0901(LogObject, DbConnectionInf, FileName);
x.LoadRecordsFromFile();
}
public static string GetUnderlyer(DBConnectionDetails conn, int posId)
{
return(DbUtils.ObtainCmdAndEval(conn.ConnectionString(), cmd => (string)DbUtils.ExecuteScalar(false, cmd, @"
--select 'A/C='+pd.firmAccount + ', RIC='+ric.altId + ', Uccy='+undfi.currency + ', RIC2='+ric2.altId + ', FutMult='+cast(fut.multiplier as varchar(20)) + ', CB {type='+ct.mnem + ', initConvPrc='+cast(c.initialConversionPrice as varchar(20)) + ', FaceVal='+cast(c.faceValue as varchar(20)) + ', tradesGross='+c.tradesGross + '}'
select 'A/C='+pd.firmAccount + ', RIC='+ric.altId + ', RIC2='+ric2.altId + ', FutMult='+cast(fut.multiplier as varchar(20)) + ', CB {type='+ct.mnem + ', initConvPrc='+cast(c.initialConversionPrice as varchar(20)) + ', FaceVal='+cast(c.faceValue as varchar(20)) + ', tradesGross='+c.tradesGross + '}'
from externalPositionMap epm
join positionDescription pd on pd.posId=epm.posId
join ETSderivative der on der.derId = epm.secId
-- join ETSfinancialInstrument undfi on undfi.id = der.undId and undfi.validUntil=99999999
left outer join ETSforward fut on fut.id=der.undId and fut.validUntil=99999999
left outer join ETSalternativeIdentifier ric on ric.id=der.undId and ric.type='RIC'
--left outer join ETSalternativeIdentifier cus on cus.id=der.undId and cus.type='CUS'
left outer join ETSderivative der2 on der2.derId = der.undId
left outer join ETSalternativeIdentifier ric2 on ric2.id=der2.undId and ric2.type='RIC'
left outer join CDM_basket_detail bd on bd.secId=der.undId
left outer join CDM_basket_list bl on bl.bskt_name=bd.bskt_name
left outer join ETSconvertible c on c.id=bl.undId and c.validUntil=99999999
left outer join ETSconvertibleType ct on ct.code=c.type
where epm.posId = " + posId,
null)));
}
public static string GetPfsId(DBConnectionDetails conn, int posId)
{
return(DbUtils.ObtainCmdAndEval(conn.ConnectionString(), cmd => (string)DbUtils.ExecuteScalar(false, cmd, "select externalId from externalPositionMap where posId = " + posId, null)));
}
public static List <PaaData> ReadPaaData(DBConnectionDetails conn, int posId, int fromDate, int tillDate = 0)
{
if (tillDate < fromDate)
{
tillDate = Utility.DateToRover8(DateTime.Today);
}
var query = string.Format(@"select pq.eodDate, pq.posId, pq.quantity, r.spot, r.delta, r.theta, r.markOK
, pl.fairValue, pl.averageCost, pl.interest, pl.coupon, pl.dividend, pl.realised, pl.commission, pl.extraordinary EOPL, pl.thetaPL, pl.plOK
, pla.averageCost avgCostA, pla.realised rlsdA, pla.commission commA
, ple.extraordinary EOPLE, plrd.accruedInterest AccIntPLRD, plrd.coupon couponPLRD, plrd.dividend DivPLRD
, pl.currency CCY
, dc.result DayCount, lr.result LiborRate, lp.result LiborPrem, ep.result EquityPrem, dp.result DivPrem
from ARCH..pfsTradedQuantity pq
join ARCH..risk r on r.eodDate = pq.eodDate and r.posId=pq.posId
join ARCH..profitLoss pl on pl.eodDate = pq.eodDate and pl.posId=pq.posId
left outer join ARCH..plArchive pla on pla.eodDate = pq.eodDate and pla.posId=pq.posId
left outer join ARCH..profitLossExtraordinary ple on ple.eodDate = pq.eodDate and ple.posId=pq.posId
left outer join ARCH..profitLossRawData plrd on plrd.eodDate = pq.eodDate and plrd.posId=pq.posId
left outer join ARCH..flexibleModelResults dc on dc.eodDate = pq.eodDate and dc.posId=pq.posId and dc.resultId = 170
left outer join ARCH..flexibleModelResults lr on lr.eodDate = pq.eodDate and lr.posId=pq.posId and lr.resultId = 13
left outer join ARCH..flexibleModelResults lp on lp.eodDate = pq.eodDate and lp.posId=pq.posId and lp.resultId = 8
left outer join ARCH..flexibleModelResults ep on ep.eodDate = pq.eodDate and ep.posId=pq.posId and ep.resultId = 9
left outer join ARCH..flexibleModelResults dp on dp.eodDate = pq.eodDate and dp.posId=pq.posId and dp.resultId = 5
where pq.eodDate between {1} and {2}
and pq.posId={0}
order by pq.eodDate
", posId, fromDate, tillDate);
//query.Dump();
return(DbUtils.ExecuteReader(conn, query,
ri => new
{
EodDate = ri.GetOrdinal("eodDate"),
PosId = ri.GetOrdinal("posId"),
Quantity = ri.GetOrdinal("quantity"),
Spot = ri.GetOrdinal("spot"),
Delta = ri.GetOrdinal("delta"),
Theta = ri.GetOrdinal("theta"),
MOK = ri.GetOrdinal("markOK"),
FairValue = ri.GetOrdinal("fairValue"),
AverageCost = ri.GetOrdinal("averageCost"),
Interest = ri.GetOrdinal("interest"),
Coupon = ri.GetOrdinal("coupon"),
Dividend = ri.GetOrdinal("dividend"),
Realised = ri.GetOrdinal("realised"),
Commission = ri.GetOrdinal("commission"),
EOPL = ri.GetOrdinal("EOPL"),
ThetaPL = ri.GetOrdinal("thetaPL"),
PLOK = ri.GetOrdinal("plOK"),
AvgCostA = ri.GetOrdinal("avgCostA"),
RlsdA = ri.GetOrdinal("rlsdA"),
CommA = ri.GetOrdinal("commA"),
EOPLE = ri.GetOrdinal("EOPLE"),
AccIntPLRD = ri.GetOrdinal("AccIntPLRD"),
CouponPLRD = ri.GetOrdinal("couponPLRD"),
DivPLRD = ri.GetOrdinal("DivPLRD"),
CCY = ri.GetOrdinal("CCY"),
DayCount = ri.GetOrdinal("DayCount"),
LiborRate = ri.GetOrdinal("LiborRate"),
LiborPrem = ri.GetOrdinal("LiborPrem"),
EquityPrem = ri.GetOrdinal("EquityPrem"),
DivPrem = ri.GetOrdinal("DivPrem"),
},
(r, ri) =>
{
return new PaaData
{
EodDate = r.GetInt32(ri.EodDate),
PosId = r.GetInt32(ri.PosId),
Quantity = r.GetDouble(ri.Quantity),
Spot = Math.Round(r.GetDouble(ri.Spot), 4),
Delta = r.GetDouble(ri.Delta),
Theta = Utility.Round(r.GetDouble(ri.Theta)),
MOK = r.GetString(ri.MOK),
FairValue = Utility.Round(r.GetDouble(ri.FairValue)),
AverageCost = Math.Round(r.GetDouble(ri.AverageCost), 6),
Interest = Utility.Round(r.GetDouble(ri.Interest)),
Coupon = Utility.Round(r.GetDouble(ri.Coupon)),
Dividend = Utility.Round(r.GetDouble(ri.Dividend)),
Realised = Utility.Round(r.GetDouble(ri.Realised)),
Commission = Utility.Round(r.GetDouble(ri.Commission)),
EOPL = Utility.Round(r.GetDouble(ri.EOPL)),
EOPLE = Utility.Round(r.IsDBNull(ri.EOPLE) ? 0.0 : r.GetDouble(ri.EOPLE)),
ThetaPL = Utility.Round(r.GetFloat(ri.ThetaPL)),
PLOK = r.GetString(ri.PLOK),
AvgCostA = (r.IsDBNull(ri.AvgCostA) ? 0.0 : Math.Round(r.GetDouble(ri.AvgCostA), 6)),
RlsdA = Utility.Round(r.IsDBNull(ri.RlsdA) ? 0.0 : r.GetFloat(ri.RlsdA)),
CommA = Utility.Round(r.IsDBNull(ri.CommA) ? 0.0 : r.GetFloat(ri.CommA)),
AccIntPLRD = Utility.Round(r.IsDBNull(ri.AccIntPLRD) ? 0.0 : r.GetFloat(ri.AccIntPLRD)),
//CouponPLRD = Utility.Round(r.IsDBNull(ri.CouponPLRD) ? 0.0 : r.GetFloat(ri.CouponPLRD)),
DivPLRD = Utility.Round(r.IsDBNull(ri.DivPLRD) ? 0.0 : r.GetFloat(ri.DivPLRD)),
CCY = r.GetString(ri.CCY),
DayCount = Utility.Round(r.IsDBNull(ri.DayCount) ? 0.0 : r.GetDouble(ri.DayCount)),
LiborRate = (r.IsDBNull(ri.LiborRate) ? 0.0 : r.GetDouble(ri.LiborRate)),
LiborPrem = Utility.Round(r.IsDBNull(ri.LiborPrem) ? 0.0 : r.GetDouble(ri.LiborPrem)),
EquityPrem = Utility.Round(r.IsDBNull(ri.EquityPrem) ? 0.0 : r.GetDouble(ri.EquityPrem)),
DivPrem = Utility.Round(r.IsDBNull(ri.DivPrem) ? 0.0 : r.GetDouble(ri.DivPrem)),
};
},
30).ToList());
}
