public static DataTable GetAllStockEntityPricesAroundDates(int setID, int?seID, DateTime startsOn, DateTime?endsOn, int daysBefore, int daysAfter)
{
DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Argaam_AnalyticsConnectionString"].ConnectionString))
{
sqlConn.Open();
SqlCommand sqlCmd = new SqlCommand();
SqlDataAdapter sqlDa = new SqlDataAdapter();
sqlCmd = new SqlCommand("SP_Q4_PricesAroundEventDate_Pivoted", sqlConn);
sqlCmd.Parameters.Add(new SqlParameter("@p_event_date", startsOn));
sqlCmd.Parameters.Add(new SqlParameter("@p_event_end_date", endsOn));
sqlCmd.Parameters.Add(new SqlParameter("@p_days_before", daysBefore * -1));
sqlCmd.Parameters.Add(new SqlParameter("@p_days_after", daysAfter));
sqlCmd.Parameters.Add(new SqlParameter("@p_se_type_id", setID));
sqlCmd.Parameters.Add(new SqlParameter("@p_se_id", seID));
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand = sqlCmd;
sqlDa.Fill(dt);
}
DataTable result = new DataTable();
result.Columns.Add(new DataColumn("EntityName", typeof(String)));
result.Columns.Add(new DataColumn("CloseChangeBefore", typeof(decimal)));
result.Columns.Add(new DataColumn("CloseChangeAfter", typeof(decimal)));
result.Columns.Add(new DataColumn(startsOn.ToShortDateString(), typeof(String)));
result.Columns.Add(new DataColumn(endsOn.HasValue ? endsOn.Value.ToShortDateString() : "", typeof(String)));
result.Columns.Add(new DataColumn("EntityID", typeof(int)));
for (int i = 1; i < dt.Rows.Count; i++)
{
try
{
DataRow drOrig = dt.Rows[i];
int currentSeID = (int)drOrig[0];
String seName = FintechService.GetStockEntity(setID, currentSeID).NameEn;
int eventDateCol = daysBefore + 1;
while (eventDateCol > 0 && drOrig[eventDateCol] == DBNull.Value)
{
eventDateCol--;
}
decimal eventDateClosingPrice = (decimal)drOrig[eventDateCol];
int firstCol = 1;
while (firstCol < eventDateCol && drOrig[firstCol] == DBNull.Value)
{
firstCol++;
}
decimal firstClosingPrice = (decimal)drOrig[firstCol];
int eventEndDateColumn = eventDateCol;
if (endsOn.HasValue)
{
eventEndDateColumn = daysBefore + 2;
while (eventDateCol < dt.Columns.Count && drOrig[eventEndDateColumn] == DBNull.Value)
{
eventEndDateColumn++;
}
}
decimal eventEndDateClosingPrice = (decimal)drOrig[eventEndDateColumn];
int lastCol = dt.Columns.Count - 1;
while (lastCol > eventEndDateColumn && drOrig[lastCol] == DBNull.Value)
{
lastCol--;
}
decimal lastClosingPrice = (decimal)drOrig[lastCol];
DataRow dr = result.NewRow();
dr[0] = seName;
dr[1] = (((eventDateClosingPrice - firstClosingPrice) / firstClosingPrice) * 100);
dr[2] = (((lastClosingPrice - eventEndDateClosingPrice) / eventEndDateClosingPrice) * 100);
dr[5] = currentSeID;
result.Rows.Add(dr);
}
catch (Exception ex)
{
}
}
return(result);
}
public static List <SP_Q5_StockEntityTypeUpAndDownMonthsResult> StockEntityTypesWhichWereUpMoreThanEnnPercentOfTheTime(int setID, int fromYear, int toYear, decimal percent)
{
using (ArgaamAnalyticsDataContext aadc = new ArgaamAnalyticsDataContext())
{
var stockEntities = FintechService.GetStockEntities(setID);
var result = aadc.SP_Q5_StockEntityTypeUpAndDownMonths(fromYear, toYear, setID).ToList();
List <SP_Q5_StockEntityTypeUpAndDownMonthsResult> retVal = new List <SP_Q5_StockEntityTypeUpAndDownMonthsResult>();
var distinctSeIDs = (from r in result
select r.StockEntityID).Distinct().ToList();
foreach (int seID in distinctSeIDs)
{
var seRecords = (from r in result where r.StockEntityID == seID select r).ToList();
SP_Q5_StockEntityTypeUpAndDownMonthsResult summaryRow = new SP_Q5_StockEntityTypeUpAndDownMonthsResult();
summaryRow.StockEntityName = FintechService.GetStockEntity(setID, seID).NameEn;
summaryRow.StockEntityID = seID;
summaryRow.Year = 0;
decimal[] positiveItems = new decimal[12]; // Enumerable.Repeat(0, 12).ToArray();
decimal[] totalItems = new decimal[12];
int startYear = 0;
int endYear = 0;
decimal yearsActive = 0;
int monthsActive = 0;
foreach (var item in seRecords)
{
if (startYear == 0)
{
startYear = item.Year.Value;
}
endYear = item.Year.Value;
for (int i = 1; i <= 12; i++)
{
decimal?value = (decimal?)item.GetType().GetProperty("_" + i.ToString()).GetValue(item);
if (value.HasValue)
{
totalItems[i - 1]++;
if (value.Value > 0)
{
positiveItems[i - 1]++;
}
monthsActive++;
}
}
if (monthsActive < 12)
{
yearsActive += (decimal)monthsActive / 12;
}
else
{
yearsActive++;
}
}
bool includeSummary = false;
for (int i = 1; i <= 12; i++)
{
if (totalItems[i - 1] > 0)
{
var percentUp = positiveItems[i - 1] / totalItems[i - 1] * 100;
summaryRow.GetType().GetProperty("_" + i.ToString()).SetValue(summaryRow, new System.Nullable <Decimal>(percentUp));
if (percentUp >= percent)
{
includeSummary = true;
}
}
else
{
summaryRow.GetType().GetProperty("_" + i.ToString()).SetValue(summaryRow, new System.Nullable <Decimal>(0));
}
}
if (includeSummary)
{
summaryRow.StartYear = startYear;
summaryRow.EndYear = endYear;
summaryRow.YearsActive = yearsActive;
retVal.Add(summaryRow);
}
}
retVal = retVal.OrderByDescending(r => r.YearsActive).ToList();
return(retVal);
}
}
public static List <TableRowViewModel> GetStockEntityPricesAroundDates_UI(int setID, int seID, DateTime startsOn, DateTime?endsOn, int daysBefore, int daysAfter)
{
var result = new List <TableRowViewModel>();
var isRange = endsOn != null;
var startsOnShortDateString = startsOn.ToShortDateString();
var endsOnShortDateString = isRange ? endsOn.Value.ToShortDateString() : "";
using (ArgaamAnalyticsDataContext aadc = new ArgaamAnalyticsDataContext())
{
var spResult = (aadc.SP_Q4_PricesAroundEventDate(startsOn, endsOn, daysBefore, daysAfter, seID, setID)).ToList();
var resultDates = (from r in spResult
group r by new { r.ForDate, r.DoW }
into grp
select new DateWithDoW
{
ForDate = grp.Key.ForDate.Value,
DoW = grp.Key.DoW
}).ToList();
var seName = FintechService.GetStockEntity(setID, seID).NameEn;
#region Compute EventDate Price or Last Valid price; Compute Fist and Last Valid Closing prices
bool eventDateClosingPriceWasZero = false;
decimal?eventDateClosingPrice = (from r in spResult
where r.seID == seID && r.setID == setID
select r.Close).Skip(daysBefore).Take(1).First();
DateTime closingPriceAltDate;
decimal? firstValidClosingPrice = 0.0M;
DateTime firstValidClosingPriceDate = DateTime.MinValue;
decimal? lastValidClosingPrice = 0.0M;
DateTime lastValidClosingPriceDate = DateTime.MinValue;
int firstValidPriceIndex = -1;
int lastValidPriceIndex = -1;
if (eventDateClosingPrice.Value == 0)
{
// Pick up the last closing value that was not 0
eventDateClosingPriceWasZero = true;
var a = (from r in spResult
where r.seID == seID && r.setID == setID &&
r.ForDate < startsOn &&
r.Close != 0
orderby r.ForDate descending
select new
{
r.ForDate,
r.Close
}).Take(1).First();
eventDateClosingPrice = a.Close;
closingPriceAltDate = a.ForDate.Value;
}
var b = (from r in spResult
where r.seID == seID && r.setID == setID &&
r.Close != 0
orderby r.ForDate
select new
{
r.ForDate,
r.Close
}).Take(1).First();
firstValidClosingPrice = b.Close;
firstValidClosingPriceDate = b.ForDate.Value;
var c = (from r in spResult
where r.seID == seID && r.setID == setID &&
r.Close != 0
orderby r.ForDate descending
select new
{
r.ForDate,
r.Close
}).Take(1).First();
lastValidClosingPrice = c.Close;
lastValidClosingPriceDate = c.ForDate.Value;
#endregion
#region Row 1 Header
TableRowViewModel row1 = new TableRowViewModel();
row1.TableCells.Add(new TableCellViewModel()
{
Text = "Dates / Entity"
});
int startsOnIndex = -1, endsOnIndex = -1;
int actualEventDatePriceIndex = -1;
for (int i = 0; i < resultDates.Count; i++)
{
var resultForDate = resultDates[i].ForDate;
var resultForDateShortDateString = resultForDate.ToShortDateString();
var cell = new TableCellViewModel()
{
Text = resultForDate.ToString("dd MM yyyy") + " " + resultDates[i].DoW
};
if (resultForDateShortDateString == startsOnShortDateString)
{
cell.BackgroundColor = "lightblue";
startsOnIndex = i;
}
else if (isRange)
{
if (resultForDate > startsOn && resultForDate < endsOn)
{
if (i == startsOnIndex + 1)
{
cell.Text = "...";
}
else
{
continue;
}
}
else if (resultForDateShortDateString == endsOnShortDateString)
{
cell.BackgroundColor = "lightblue";
endsOnIndex = i;
}
}
row1.TableCells.Add(cell);
}
result.Add(row1);
#endregion
List <StockEntityAndType> seArray = new List <StockEntityAndType>() /*
* {
* new StockEntityAndType() { StockEntityTypeID = 5, StockEntityID = 1 },
* new StockEntityAndType() { StockEntityTypeID = 5, StockEntityID = 3 }
* }*/;
seArray.Add(new StockEntityAndType()
{
StockEntityTypeID = setID, StockEntityID = seID
});
decimal beforeEventChange = 0.0M, afteEventChange = 0.0M;
#region Row 2 & 3 Brent & Crude
foreach (var se in seArray)
{
bool isCompany = false;
TableRowViewModel row = new TableRowViewModel();
if (se.StockEntityTypeID == 5 && se.StockEntityID == 1)
{
row.TableCells.Add(new TableCellViewModel()
{
Text = "BRENT"
});
}
else if (se.StockEntityTypeID == 5 && se.StockEntityID == 3)
{
row.TableCells.Add(new TableCellViewModel()
{
Text = "CRUDE"
});
}
else
{
row.TableCells.Add(new TableCellViewModel()
{
Text = seName
});
isCompany = true;
}
var filteredResults = spResult.Where(r => r.setID == se.StockEntityTypeID && r.seID == se.StockEntityID).ToList();
for (int i = 0; i < filteredResults.Count; i++)
{
var item = filteredResults[i];
var cell = new TableCellViewModel()
{
Text = item.Close == 0 ? "" : item.Close.ToString()
};
if (isCompany && firstValidPriceIndex == -1 && item.Close != 0)
{
firstValidPriceIndex = i;
}
if (isCompany && item.Close != 0)
{
lastValidPriceIndex = i;
}
if (i == startsOnIndex || i == endsOnIndex)
{
cell.BackgroundColor = "lightblue";
if (i == startsOnIndex && isCompany && eventDateClosingPriceWasZero)
{
int reverseIndex = i;
do
{
reverseIndex--;
if (filteredResults[reverseIndex].Close != 0)
{
break;
}
} while (true);
for (int j = reverseIndex; j < i; j++)
{
// J+1 cos the first cell is an info cell
row.TableCells[j + 1].BackgroundColor = "lightblue";
}
}
}
if (i > startsOnIndex && i < endsOnIndex)
{
if (i == startsOnIndex + 1)
{
cell.Text = "...";
}
else
{
continue;
}
}
row.TableCells.Add(cell);
}
result.Add(row);
}
#endregion
TableRowViewModel row4 = new TableRowViewModel();
row4.TableCells.Add(new TableCellViewModel());
//bool closingRange
for (int i = 0; i < resultDates.Count; i++)
{
TableCellViewModel cell = new TableCellViewModel();
if (i == firstValidPriceIndex)
{
var firstClosingPriceChangePercent = (((eventDateClosingPrice - firstValidClosingPrice) / firstValidClosingPrice) * 100);
cell.Text = firstClosingPriceChangePercent.Value.ToString("0.00") + "%";
cell.BackgroundColor = firstClosingPriceChangePercent >= 0 ? "lightgreen" : "red";
cell.FontColor = firstClosingPriceChangePercent >= 0 ? "" : "white";
cell.ColSpan = startsOnIndex - firstValidPriceIndex;
}
else if ((i > firstValidPriceIndex && i < startsOnIndex) ||
(((!isRange && i > startsOnIndex + 1) || (isRange && i > endsOnIndex + 1)) && i <= lastValidPriceIndex) ||
(isRange && i > startsOnIndex && i < endsOnIndex)
)
{
continue;
}
else if (i == startsOnIndex)
{
cell.BackgroundColor = "lightblue";
}
else if (isRange && i == endsOnIndex)
{
TableCellViewModel cellRange = new TableCellViewModel();
cellRange.Text = "...";
cell.BackgroundColor = cellRange.BackgroundColor = "lightblue";
row4.TableCells.Add(cellRange);
}
else if (((!isRange && i == startsOnIndex + 1) || (isRange && i == endsOnIndex + 1)) && i <= lastValidPriceIndex)
{
var lastClosingPriceChangePercent = (((lastValidClosingPrice - eventDateClosingPrice) / eventDateClosingPrice) * 100);
cell.Text = lastClosingPriceChangePercent.Value.ToString("0.00") + "%";
cell.BackgroundColor = lastClosingPriceChangePercent >= 0 ? "lightgreen" : "red";
cell.FontColor = lastClosingPriceChangePercent >= 0 ? "" : "white";
cell.ColSpan = lastValidPriceIndex - ((isRange) ? endsOnIndex : startsOnIndex);
}
row4.TableCells.Add(cell);
}
result.Add(row4);
}
return(result);
}