public DataTable GetClassificationData()
{
DataTable DT = new DataTable();
SqlCeDataReader reader = SourceDataAccess.GetData("SELECT * FROM Classification");
DT.Load(reader);
return(DT);
}
public List <string> GetFunds()
{
List <string> funds = new List <string>();
SqlCeDataReader readerFunds = SourceDataAccess.GetData("SELECT DISTINCT(Fund) AS FUNDS FROM ADMIN");
while (readerFunds.Read())
{
funds.Add(readerFunds[0].ToString());
}
return(funds);
}
public DataTable GetDashboardData(string source)
{
SqlCeDataReader reader;
DataTable dt = new DataTable();
if (source.ToLower() == "admin")
{
reader = SourceDataAccess.GetData("SELECT * FROM Admin");
}
else
{
reader = SourceDataAccess.GetData("SELECT * FROM Manager");
}
dt.Load(reader);
return(dt);
}
public DataTable GenerateMainReportTable(DateTime dateGenerated, string AQRID)
{
string SQLMAIN =
@"SELECT LOOKUP, SOURCE, SUM(localprice) AS LOCALPRICE, DATEGENERATED from " +
@"(SELECT 'MANAGER' AS SOURCE, cls.LookUp AS LOOKUP, mgr.Product AS PRODUCT, SUM(mgr.EndingMarketValue) AS LOCALPRICE, mgr.DateGenerated as DATEGENERATED " +
@"FROM Manager AS mgr INNER JOIN Classification AS cls ON cls.Name = mgr.Product " +
@"WHERE mgr.Fund = @Fund {0} GROUP BY mgr.Product, cls.LookUp, mgr.DateGenerated " +
@"UNION " +
@"SELECT 'ADMIN' AS SOURCE, cls.LookUp AS lookup, adm.Product AS PRODUCT, SUM(adm.BaseMktValue) AS LOCALPRICE, adm.DateGenerated as DATEGENERATED " +
@"FROM Admin AS adm INNER JOIN Classification AS cls ON cls.Name = adm.Product " +
@"WHERE adm.Fund = @Fund {1} GROUP BY adm.Product, cls.LookUp, adm.DateGenerated " +
@") as MAIN WHERE CONVERT(datetime,DATEGENERATED,110) = @DateGenerated " +
@"GROUP BY SOURCE, LOOKUP, DATEGENERATED";
DataTable mainTable = new DataTable();
DataRow tempDataRow;
mainTable.Columns.Add(new DataColumn("BreakDown"));
mainTable.Columns.Add(new DataColumn("Portfolio"));
mainTable.Columns.Add(new DataColumn("Fund Admin"));
mainTable.Columns.Add(new DataColumn("Geneva NAV"));
mainTable.Columns.Add(new DataColumn("Fund Admin NAV"));
mainTable.Columns.Add(new DataColumn("NAV Diff (bps)"));
mainTable.Columns.Add(new DataColumn("Geneva (daily) PNL"));
mainTable.Columns.Add(new DataColumn("IFS (daily) PNL"));
mainTable.Columns.Add(new DataColumn("PNL Diff (bps)"));
double diffPercentage = 0;
DataAccess DA = new DataAccess(ConnectionString);
SqlCeDataReader fundRecords;
DataTable tempDataTable = new DataTable();
tempDataRow = mainTable.NewRow();
SqlCeDataReader readerFunds = SourceDataAccess.GetData("SELECT DISTINCT(Fund) AS FUNDS FROM ADMIN");
while (readerFunds.Read())
{
tempDataRow = mainTable.NewRow();
tempDataRow["Portfolio"] = readerFunds[0];
tempDataRow["Fund Admin"] = "IFS";
DA.Command.Parameters.Clear();
DA.Command.Parameters.AddWithValue("@DateGenerated", dateGenerated);
DA.Command.Parameters.AddWithValue("@Fund", readerFunds[0]);
if (!string.IsNullOrEmpty(AQRID))
{
SQLMAIN = string.Format(SQLMAIN, INVST_WHERE_CLAUSE, AQRID_WHERE_CLAUSE);
DA.Command.Parameters.AddWithValue("@AQRID", AQRID);
}
else
{
SQLMAIN = string.Format(SQLMAIN, string.Empty, string.Empty);
}
fundRecords = DA.GetData(SQLMAIN);
tempDataTable = new DataTable();
tempDataTable.Load(fundRecords);
var managerRows = from row in tempDataTable.AsEnumerable()
where row[1].ToString() == "MANAGER"
select row;
var managerTotal = managerRows.AsEnumerable().Sum(a => Convert.ToDouble(a[2]));
var adminRows = from row in tempDataTable.AsEnumerable()
where row[1].ToString() == "ADMIN"
select row;
var adminTotal = adminRows.AsEnumerable().Sum(a => Convert.ToDouble(a[2]));
diffPercentage = ((managerTotal - adminTotal) / managerTotal) * 100;
tempDataRow["Geneva (daily) PNL"] = Utilities.FormatCellValue(managerTotal);
tempDataRow["IFS (daily) PNL"] = Utilities.FormatCellValue(adminTotal);
tempDataRow["PNL Diff (bps)"] = Utilities.FormatCellValue(diffPercentage, true);
mainTable.Rows.Add(tempDataRow);
}
DA.CloseConnection();
readerFunds.Close();
mainTable.AcceptChanges();
return(mainTable);
}