public static void Initialize(QueryModel query, EDataTable timeSliceTable, EDataTable snapShootTable, DataTable queryData)
{
_timeSliceFormulas = query.TimeSlice.Formulas.ToList();
_snapShootFormulas = query.SnapShoot.Formulas.ToList();
_timeSliceTable = timeSliceTable;
_snapShootTable = snapShootTable;
_queryData = queryData;
}
public static void Initialize(QueryModel query, EDataTable timeSliceTable, EDataTable snapShootTable, DataTable queryData)
{
_timeSliceFormulas = query.TimeSlice.Formulas.ToList();
_snapShootFormulas = query.SnapShoot.Formulas.ToList();
_timeSliceTable = timeSliceTable;
_snapShootTable = snapShootTable;
_queryData = queryData;
}
public void ClearAll()
{
_periodStructs.Clear();
ETableDictionary.ToList().Clear();
_tempList.ToList().Clear();
_sortedList.Clear();
_days.Clear();
_eDataTableDictionary.Clear();
_headerTable = null;
ETableDictionary.Clear();
}
public void CreateSnapShotTables(List<string> days, List<TimeSpan> timeSpans)
{
if (ETableDictionary.Count() != 0)
ClearAll();
_headerTable = new EDataTable(_queryId, 1, true, false);
_headerTable.Columns.Add("Date");
_headerTable.Columns[0].DataType = typeof(string);
_headerTable.Columns.Add("Time");
_headerTable.Columns[1].DataType = typeof(string);
_headerTable.Columns.Add("Day");
_headerTable.Columns[2].DataType = typeof(string);
_headerTable.TableName = _internalTable.TableName + "SnapShot ";
ETableDictionary.Add(_internalTable.TableName + "SnapShot "
, _headerTable);
_days = days;
_timeSpans = timeSpans;
var globalResult = new List<DataRow>();
var tempTable = new EDataTable(_queryId, true, false);
foreach (DataColumn dataColumn in _internalTable.Columns)
{
tempTable.Columns.Add(dataColumn.ColumnName);
}
var dayresult = from seldays in _internalTable.AsEnumerable()
where (_days.Contains(seldays.Field<DateTime>("BarTime").DayOfWeek.ToString()))
select seldays;
if (!dayresult.Any()) return;
foreach (var timespan in _timeSpans)
{
var timespan1 = timespan;
var sdayResult = from items in dayresult
where
items.Field<DateTime>("BarTime").Hour == timespan1.Hours
&& items.Field<DateTime>("BarTime").Minute == timespan1.Minutes
select items;
if (sdayResult.Any())
globalResult.AddRange(sdayResult);
}
_tempList = from items in globalResult select items;
if (!_tempList.Any()) return;
var sday = globalResult[0].Field<DateTime>("BarTime").DayOfYear;
DispositionDays(sday);
var result = _sortedList;
foreach (var dataRow in result.OrderBy(oo => oo.ToList()[0].Field<DateTime>("BarTime").DayOfYear))
{
if (!dataRow.Any()) continue;
foreach (var row in dataRow)
{
var drow = tempTable.NewRow();
drow.ItemArray = row.ItemArray;
tempTable.Rows.Add(drow);
var arritem = new ArrayList()
{
row.Field<DateTime>("BarTime").ToString(
CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern),
row.Field<DateTime>("BarTime").TimeOfDay,
row.Field<DateTime>("BarTime").DayOfWeek.ToString()
};
var hdrow = _headerTable.NewRow();
hdrow.ItemArray = arritem.ToArray();
_headerTable.Rows.Add(hdrow);
}
}
tempTable.Columns.Remove("BarTime");
tempTable.TableName = _internalTable.TableName + " SnapShot TimeFrames";
ETableDictionary.Add(_internalTable.TableName + " SnapShot Results", tempTable);
}
private void NormalizeEDataTables(DataType dataType)
{
var summaryDays = new List<DataRow>();
foreach(var day in _days)
{
foreach (var tempdays in _eDataTableDictionary.Select(etable => etable.Value.AsEnumerable().OrderBy(oo => oo.Field<DateTime>("Time").DayOfYear)))
{
summaryDays.AddRange((from eday in tempdays
where eday.Field<DateTime>("Time").DayOfWeek.ToString() == day
select eday).ToList());
}
}
var sortedsummary = summaryDays.OrderBy(oo => oo.Field<DateTime>("Time").DayOfWeek);
foreach (var summaryDay in sortedsummary)
{
var drow = _headerTable.NewRow();
var sdate =
summaryDay.Field<DateTime>("Time").ToString(
CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern);
var sday = summaryDay.Field<DateTime>("Time").DayOfWeek.ToString();
var rowitems = new ArrayList()
{
sdate,
sday
};
drow.ItemArray = rowitems.ToArray();
if(!_headerTable.AsEnumerable().ToList().Exists(o => o.Field<string>("Date") == drow.Field<string>("Date")))
_headerTable.Rows.Add(drow);
}
foreach (var editem in _eDataTableDictionary)
{
var dtable = new EDataTable(_queryId, false, true);
foreach (var column in editem.Value.Columns.Cast<DataColumn>().Where(column => column.ColumnName != "BarTime"))
{
dtable.Columns.Add(column.ColumnName);
}
ETableDictionary.Add(editem.Key,dtable);
var currtables = from crtable in ETableDictionary where crtable.Key == editem.Key select crtable.Value;
var currtable = currtables.ToList()[0];
var indexer = 0;
foreach(DataRow rowItem in _headerTable.Rows)
{
var dtRow = currtable.NewRow();
if (editem.Value.AsEnumerable().ToList().Exists(p => p.Field<DateTime>("Time").ToString(
CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern) ==
rowItem.Field<string>("Date")))
{
if (dataType == DataType.Bar)
{
if (_internalTable.Columns.Contains("OpenValue"))
{
var open = editem.Value.Rows[indexer].Field<float>("OpenValue");
dtRow["OpenValue"] = open;
}
if (_internalTable.Columns.Contains("HighValue"))
{
var high = editem.Value.Rows[indexer].Field<float>("HighValue");
dtRow["HighValue"] = high;
}
if (_internalTable.Columns.Contains("LowValue"))
{
var low = editem.Value.Rows[indexer].Field<float>("LowValue");
dtRow["LowValue"] = low;
}
if (_internalTable.Columns.Contains("CloseValue"))
{
var close = editem.Value.Rows[indexer].Field<float>("CloseValue");
dtRow["CloseValue"] = close;
}
if (_internalTable.Columns.Contains("ActualVol"))
{
var actvol = editem.Value.Rows[indexer]["ActualVol"];
dtRow["ActualVol"] = actvol;
}
if (_internalTable.Columns.Contains("TickVol"))
{
var tickvolume = editem.Value.Rows[indexer]["TickVol"];
dtRow["TickVol"] = tickvolume;
}
if (_internalTable.Columns.Contains("AskVol"))
{
var askvol = editem.Value.Rows[indexer]["AskVol"];
dtRow["AskVol"] = askvol;
}
if (_internalTable.Columns.Contains("BidVol"))
{
var bidvol = editem.Value.Rows[indexer]["BidVol"];
dtRow["BidVol"] = bidvol;
}
}
if (dataType==DataType.Tick)
{
if (_internalTable.Columns.Contains("Bid"))
{
var open = editem.Value.Rows[indexer]["Bid"];
dtRow["Bid"] = open;
}
if (_internalTable.Columns.Contains("Ask"))
{
var open = editem.Value.Rows[indexer]["Ask"];
dtRow["Ask"] = open;
}
if (_internalTable.Columns.Contains("BidVol"))
{
var open = editem.Value.Rows[indexer]["BidVol"];
dtRow["BidVol"] = open;
}
if (_internalTable.Columns.Contains("AskVol"))
{
var open = editem.Value.Rows[indexer]["AskVol"];
dtRow["AskVol"] = open;
}
if (_internalTable.Columns.Contains("Trade"))
{
var open = editem.Value.Rows[indexer]["Trade"];
dtRow["Trade"] = open;
}
if (_internalTable.Columns.Contains("TradeVol"))
{
var open = editem.Value.Rows[indexer]["TradeVol"];
dtRow["TradeVol"] = open;
}
}
currtable.Rows.Add(dtRow);
indexer++;
}
else
{
dtRow.ItemArray = new ArrayList(){"-" ,"-","-","-"}.ToArray();
currtable.Rows.Add(dtRow);
}
}
}
}
private void AddTimeSliceTableColumns(EDataTable edataTable,List<string> selectedColumns )
{
edataTable.Columns.Add("Time");
edataTable.Columns["Time"].DataType = typeof(DateTime);
foreach (var selectedColumn in selectedColumns.Where(selectedColumn => selectedColumn != "Time"))
{
edataTable.Columns.Add(selectedColumn);
edataTable.Columns[selectedColumn].DataType = typeof(float);
}
}
public void CreateTimeSliceTables(List<string> days, IEnumerable<string> periods,DataType tableType,List<string> selectedColumns )
{
if(ETableDictionary.Count() != 0) ClearAll();
_headerTable = new EDataTable(1, _queryId, false, true);
_headerTable.Columns.Add("Date");
_headerTable.Columns[0].DataType = typeof(string);
_headerTable.Columns.Add("Day");
_headerTable.Columns[0].DataType = typeof(string);
ETableDictionary.Add(_internalTable.TableName + " TimeSlice"
, _headerTable);
_days = days;
ParsePeriods(periods);
foreach(var period in _periodStructs)
{
var edataTable = new EDataTable(_queryId, false, true);
if(!_internalTable.AsEnumerable().Any()) return;
AddTimeSliceTableColumns(edataTable,selectedColumns);
foreach (var day in _days)
{
_sortedList.Clear();
if (!_internalTable.AsEnumerable().Any())
return;
var currentDayInDT = from rows in _internalTable.AsEnumerable()
where
rows.Field<DateTime>("BarTime").DayOfWeek.ToString() == day
select rows;
if (!currentDayInDT.Any()) continue;
var daysWithStartPeriod =
from rowss in currentDayInDT.OrderBy(oo => oo.Field<DateTime>("BarTime").DayOfYear)
where
rowss.Field<DateTime>("BarTime").TimeOfDay > period.StartPeriod
select rowss;
if (!daysWithStartPeriod.Any()) continue;
var daysInPeriod = from rowsss in daysWithStartPeriod
where rowsss.Field<DateTime>("BarTime").TimeOfDay < period.EndPeriod
select rowsss;
if (!daysInPeriod.Any()) continue;
var sday = daysInPeriod.ToList()[0].Field<DateTime>("BarTime").DayOfYear;
_tempList = from rowsss in daysInPeriod
where rowsss.Field<DateTime>("BarTime").TimeOfDay < period.EndPeriod
select rowsss;
DispositionDays(sday);
var sortedList = _sortedList;
if (!_sortedList.Any())
continue;
foreach (var varday in sortedList)
{
var drow = edataTable.NewRow();
AddTimeSliceResults(varday, tableType, @drow);
edataTable.Rows.Add(drow);
}
}
var key = period.StartPeriod.ToString() + "-" + period.EndPeriod.ToString();
_eDataTableDictionary.Add(key,edataTable);
}
NormalizeEDataTables(tableType);
}
private void AddDataTableToExcel(ExcelPackage excel, EDataTable table, ExportStyle style, ref int rowIndex, ref int columnIndex)
{
if (excel == null) throw new ArgumentNullException("excel");
var colstart = columnIndex;
var colbak = colstart;
if (style == ExportStyle.SheetWise)
{
var worksheet = excel.Workbook.Worksheets.Last();
worksheet.Name = table.TableName;
}
foreach (DataColumn col in table.Columns)
{
columnIndex += _excelStyle.ColumnSpace;
var cel = excel.Workbook.Worksheets.Last().Cells[rowIndex, columnIndex];
// cel.Style.Font..Color = ColorTranslator.ToOle(_excelStyle.HeaderBackColor);
cel.Style.Font.Color.SetColor(_excelStyle.HeaderForeColor);
cel.Style.Font.Name = _excelStyle.FontName;
cel.Style.Font.Size = _excelStyle.FontSize;
cel.Style.Font.Italic = _excelStyle.HeaderItalic;
cel.Style.Font.Bold = _excelStyle.HeaderFontBold;
cel.Style.Fill.PatternType = ExcelFillStyle.LightGray;
cel.Style.Fill.BackgroundColor.SetColor(Color.CadetBlue);
excel.Workbook.Worksheets.Last().Cells[rowIndex, columnIndex].Value = col.ColumnName;
}
var colmcount = table.Columns.Count - 1;
foreach (DataRow row in table.Rows)
{
if (ExportRowProgress != null)
ExportRowProgress(_eventRow++, _progressRowCount);
rowIndex += _excelStyle.RowSpace;
int indexer = 0;
foreach (DataColumn col in table.Columns)
{
colstart += _excelStyle.ColumnSpace;
var cel = excel.Workbook.Worksheets.Last().Cells[rowIndex, colstart];
if(indexer == colmcount)
{
var borders = cel.Style.Border;
borders.Right.Style = ExcelBorderStyle.Medium;
borders.Right.Color.SetColor(Color.Black);
}
if(col is EDataColumn)
{
cel.Style.Font.Color.SetColor(Color.Black);
cel.Style.Font.Name = _excelStyle.FontName;
cel.Style.Font.Size = _excelStyle.FontSize;
cel.Style.Font.Italic = _excelStyle.ItemItalic;
cel.Style.Font.Bold = true;// _excelStyle.ItemFontBold;
cel.Style.Fill.PatternType = ExcelFillStyle.LightGray;
cel.Style.Fill.BackgroundColor.SetColor(Color.LightSlateGray);
}
else
{
cel.Style.Font.Color.SetColor(_excelStyle.ItemForeColor);
cel.Style.Font.Name = _excelStyle.FontName;
cel.Style.Font.Size = _excelStyle.FontSize;
cel.Style.Font.Italic = _excelStyle.ItemItalic;
cel.Style.Font.Bold = _excelStyle.ItemFontBold;
}
excel.Workbook.Worksheets.Last().Cells[rowIndex, colstart].Value = row[col.ColumnName].ToString();
indexer++;
}
colstart = colbak;
}
}