Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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);
        }