public static DateTime GetLastVcssTaskScheduledTime(VcssTaskInfoModel vcssTaskInfo)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
return(entities.VcssTasks.Where(t => t.vcss_task_info_id == vcssTaskInfo.Id).OrderByDescending(t => t.Id).Select(t => t.scheduled_time).FirstOrDefault());
}
}
public static string ValidateUserProtfolioCount(string usernameORemail)
{
string result_message = null;
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
Tenant tenant = entities.Tenants.Where(t => t.username == usernameORemail || t.email == usernameORemail).FirstOrDefault();
if (tenant == null)
{
result_message = "Inavlid Action (Add Portfolio), Username does not exists";
}
string config_value = entities.Configs.Where(c => c.ConfigName == tenant.Tenant_Type.Name + "-portfolio_count").Select(c => c.ConfigValue).FirstOrDefault();
int portfolio_count = 0;
if (int.TryParse(config_value, out portfolio_count))
{
if (portfolio_count <= tenant.Portfolios.Count)
{
result_message = "Para disfrutar de todas las funcionalidades de forma ilimitada, debe ser usuario PRO. Conozca más ingresando en ";
}
}
entities.Database.Connection.Close();
}
return(result_message);
}
public static List <VcssTaskModel> GetVcssTasksToExecute(VcssTaskInfoEnum vcssTaskInfoId)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
return(entities.VcssTasks
.Include(t => t.VcssTaskInfo)
.Include(t => t.VcssTaskInfo.Market)
.Where(t => t.vcss_task_info_id == (int)vcssTaskInfoId)
.Select(t => new VcssTaskModel()
{
Id = t.Id,
data = t.data,
scheduled_time = t.scheduled_time,
vcss_task_info_id = t.vcss_task_info_id,
VcssTaskInfo = new VcssTaskInfoModel()
{
auth_token = t.VcssTaskInfo.auth_token,
last_updated = t.VcssTaskInfo.last_updated,
market_id = t.VcssTaskInfo.market_id,
name = t.VcssTaskInfo.name,
step = t.VcssTaskInfo.step,
url = t.VcssTaskInfo.url,
Id = t.VcssTaskInfo.Id,
Market = new MarketModel()
{
Id = t.VcssTaskInfo.Market.Id,
name = t.VcssTaskInfo.Market.name,
utc_offset = t.VcssTaskInfo.Market.utc_offset,
work_hours = t.VcssTaskInfo.Market.work_hours
}
}
})
.ToList <VcssTaskModel>());
}
}
public static Symbol GetSymbolInfo(int symbol_ID)
{
Symbol result = new Symbol();
using (ctaDBEntities entities = new ctaDBEntities())
{
var stock = entities.Stocks.Where(x => x.Id == symbol_ID).FirstOrDefault();
result.Symbol_ID = symbol_ID;
result.Symbol_Name = stock.symbol;
result.Symbol_Company_Name = stock.name;
result.Description = stock.description;
result.Symbol_Market_ID = stock.market_id;
result.Symbol_Market = stock.Market.name;
result.Intradiary_Info = QuotesService.GetSymbolIntradiaryInfo(symbol_ID);
var lastQuote = stock.Stock_Quote.OrderByDescending(itm => itm.date_round).FirstOrDefault();
if (lastQuote != null)
{
result.Intradiary_Info.Minimun = lastQuote.minimun;
result.Intradiary_Info.Maximun = lastQuote.maximun;
result.Intradiary_Info.Volume = (double)lastQuote.volume;
}
if (!(entities.Database.Connection.State == ConnectionState.Closed))
{
entities.Database.Connection.Close();
}
}
return(result);
}
public static string ValidateUserIndicatorCount(string usernameORemail, int portfolioID, int stockID)
{
string result_message = null;
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
Tenant tenant = entities.Tenants.Where(t => t.username == usernameORemail || t.email == usernameORemail).FirstOrDefault();
if (tenant == null)
{
result_message = "Inavlid Action (Add Indicator/Shape), Username does not exists";
}
string config_value = entities.Configs.Where(c => c.ConfigName == tenant.Tenant_Type.Name + "-indicator_count").Select(c => c.ConfigValue).FirstOrDefault();
int stock_count = 0;
if (int.TryParse(config_value, out stock_count))
{
if (stock_count <= (entities.Portfolio_Stock_Indicator.Where(i => i.portfolio_id == portfolioID && i.stock_id == stockID).Count() + (entities.Portfolio_Stock_Shape.Where(i => i.portfolio_id == portfolioID && i.stock_id == stockID).Count())))
{
result_message = "Para disfrutar de todas las funcionalidades de forma ilimitada, debe ser usuario PRO. Conozca más ingresando en ";
}
}
entities.Database.Connection.Close();
}
return(result_message);
}
private List <Candel> GetCandelsBySymbol(string symbol)
{
var result = new List <Candel>();
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
var stock_entity = entities.Stocks.Where(s => s.symbol == symbol).First();
result = stock_entity.Stock_Quote.Select(x => new Candel()
{
Date = x.date_round,
Open = x.opening,
Close = x.closing,
Minimun = x.minimun,
Maximun = x.maximun,
Volume = (double)x.volume
})
.OrderBy(y => y.Date).ToList();
entities.Database.Connection.Close();
}
return(result);
}
private static List <Shape_Indicator> GetSymbolShapes(ctaCOMMON.Charts.Symbol symbol, int shapesCount, bool initializeFromDatabase, out bool shapeUpdate)
{
List <Shape_Indicator> shapes = new List <Shape_Indicator>();
shapeUpdate = false;
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
var shapes_entities = entities.Portfolio_Stock_Shape.Where(pss => pss.portfolio_id == symbol.Portfolio_ID && pss.stock_id == symbol.Symbol_ID);
if (shapes_entities.Count() != shapesCount)
{
shapeUpdate = true;
foreach (var shape_entitie in shapes_entities)
{
Shape_Indicator shape = Shape_Indicator_Builder.BuildShapeInstance(shape_entitie.Shape.name);
shape.ID = shape_entitie.Id;
shape.Color = shape_entitie.color;
shape.Name = shape_entitie.name;
shape.End_Date = shape_entitie.date2.Value;
shape.End_Value = shape_entitie.value2.Value;
shape.Start_Date = shape_entitie.date1;
shape.Start_Value = shape_entitie.value1;
shape.Data_Source = symbol.Quotes;
shape.ApplyFormula();
shapes.Add(shape);
}
}
entities.Database.Connection.Close();
}
return(shapes);
}
private static List <Chart_Indicator> GetSymbolIndicators(ctaCOMMON.Charts.Symbol symbol, int indicatorsCount, bool initializeFromDatabase, CandelRange candelRange, out bool indicatorUpdate)
{
List <Chart_Indicator> indicators = new List <Chart_Indicator>();
indicatorUpdate = false;
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
var indicators_entities = entities.Portfolio_Stock_Indicator.Where(psi => psi.portfolio_id == symbol.Portfolio_ID && psi.stock_id == symbol.Symbol_ID);
if (indicators_entities.Count() != indicatorsCount)
{
indicatorUpdate = true;
foreach (var indicator_entitie in indicators_entities)
{
Chart_Indicator indicator = Chart_Indicator_Builder.BuildIndicatorInstance(indicator_entitie.Indicator.name);
indicator.ID = indicator_entitie.Id;
if (initializeFromDatabase)
{
indicator.InitializeFromDataBaseValues(symbol.Quotes, indicator_entitie.param1, indicator_entitie.color1, indicator_entitie.param2, indicator_entitie.color2, indicator_entitie.param3, indicator_entitie.color3, candelRange);
}
indicators.Add(indicator);
}
}
entities.Database.Connection.Close();
}
return(indicators);
}
public static Serie GetSymbolIntradiaryDataSerie(int portfolio_id, int symbol_id)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
string user_type = (portfolio_id == 0) ? "FREE" : entities.Portfolios.Where(p => p.Id == portfolio_id).Select(p => p.Tenant.Tenant_Type.Name).First();
if (user_type == "FREE")
{
return(new Serie());
}
var symbol_quotes = entities.Stock_Quote_Intradiary.Where(sq => sq.stock_id == symbol_id).OrderBy(sq => sq.datetime);
List <Candel> quotes = symbol_quotes.Select(q => new Candel()
{
Date = q.datetime,
Open = q.opening,
Close = q.opening,
Maximun = q.last_trade_price,
Minimun = q.last_trade_price,
Volume = (double)q.last_trade_size,
Visible = true
}).ToList <Candel>();
Serie result = Candel.GetDataSerie(quotes, DataSourceFieldUsed.Close, true);
entities.Database.Connection.Close();
return(result);
}
}
public static bool ActivateUser(string token)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
var tenant = entities.Tenants.Where(u => u.activationId == token).FirstOrDefault();
if (tenant != null)
{
tenant.activationId = "";
tenant.type = 2;
tenant.typeExpiration = DateTime.Now.AddMonths(1);
entities.SaveChanges();
//Send email to [email protected] to inform new activated user
string mensaje = "Se ha registrado y validado un nuevo usuario:<br/><br/><hr/>" +
"Datos de usuario:<br/>" +
"Username: "******"<br/>" +
"Email: " + tenant.email;
EmailSender.SendHTMLEmail("*****@*****.**", "*****@*****.**", "[Viciouss] Nuevo usuario registrado", mensaje);
entities.Database.Connection.Close();
return(true);
}
else
{
entities.Database.Connection.Close();
return(false);
}
}
}
public static VcssTaskInfoModel GetVcssTask(VcssTaskInfoEnum vcssTaskInfoId)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
return(entities.VcssTaskInfoes
.Include(t => t.Market)
.Where(t => t.Id == (int)vcssTaskInfoId)
.Select(t => new VcssTaskInfoModel()
{
auth_token = t.auth_token,
last_updated = t.last_updated,
market_id = t.market_id,
name = t.name,
step = t.step,
url = t.url,
Id = t.Id,
Market = new MarketModel()
{
Id = t.Market.Id,
name = t.Market.name,
utc_offset = t.Market.utc_offset,
work_hours = t.Market.work_hours
}
}).FirstOrDefault());
}
}
private void DeleteReportData(string batch)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Stock_Report.RemoveRange(entities.Stock_Report.Where(q => q.Stock.technical_report_batch == batch || batch == "ALL"));
entities.SaveChanges();
}
}
public static bool IsHoliday(int marketId, DateTime date)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
Holiday hol = entities.Holidays.Where(s => s.market_id == marketId && s.date.Year == date.Year && s.date.Month == date.Month && s.date.Day == date.Day).FirstOrDefault();
return(hol != null);
}
}
public static DateTime GetMarketCurrentLocalTime(int marketId)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
int utcoffset = entities.Markets.Where(m => m.Id == marketId).First().utc_offset;
return(DateTime.UtcNow.AddHours(utcoffset));
}
}
public static string GetUserName(string usernameORemail)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
string user_name = entities.Tenants.Where(u => (u.username == usernameORemail || u.email == usernameORemail)).Select(u => u.username).FirstOrDefault();
entities.Database.Connection.Close();
return(user_name);
}
}
public static List <TaskScheduleModel> GetPendingTasks()
{
using (ctaDBEntities entities = new ctaDBEntities())
{
return(entities.TaskSchedules.Where(ts => ts.Status == (int)TaskScheduleStatus.Pending).OrderBy(t => t.Id).Select(t => new TaskScheduleModel()
{
TaskId = t.Id, Status = (TaskScheduleStatus)t.Status, TaskType = (TaskScheduleType)t.Type, Description = t.Description, Data = t.Data, ExecTime = t.ExecTime
}).ToList());
}
}
public static void ClearIntradiaryDataByMarketID(int marketId)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
var range = entities.Stock_Quote_Intradiary.Where(sqi => sqi.Stock.market_id == marketId);
entities.Stock_Quote_Intradiary.RemoveRange(range);
entities.SaveChanges();
}
}
public static void ClearIntradiaryDataByMarketID(string market_name)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
var range = entities.Stock_Quote_Intradiary.Where(sqi => sqi.Stock.Market.name == market_name || market_name.ToUpper() == "ALL");
entities.Stock_Quote_Intradiary.RemoveRange(range);
entities.SaveChanges();
}
}
public static bool IsMarketOpen(int marketId)
{
bool result = true;
DateTime time = MarketService.GetMarketCurrentLocalTime(marketId);
if (time.DayOfWeek == DayOfWeek.Saturday || time.DayOfWeek == DayOfWeek.Sunday)
{
return(false);
}
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
var isHoliday = entities.Holidays.Where(h => h.market_id == marketId && h.date.Year == time.Year && h.date.Month == time.Month && h.date.Day == time.Day);
if (isHoliday.Count() > 0)
{
result = false;
}
else
{
string workHours = entities.Markets.Where(m => m.Id == marketId).Select(m => m.work_hours).FirstOrDefault();
if (workHours != null)
{
int startHour = int.Parse(workHours.Split(';')[0].Split(':')[0]);
int startMinutes = int.Parse(workHours.Split(';')[0].Split(':')[1]);
int closeHour = int.Parse(workHours.Split(';')[1].Split(':')[0]);
int closeMinutes = int.Parse(workHours.Split(';')[1].Split(':')[1]);
if (time.Hour < startHour)
{
result = false;
}
else if (time.Hour == startHour && time.Minute < startMinutes)
{
result = false;
}
else if (time.Hour > closeHour)
{
result = false;
}
else if (time.Hour == closeHour && time.Minute > closeMinutes)
{
result = false;
}
}
}
entities.Database.Connection.Close();
}
return(result);
}
public static UserDashboard GetDashboard(string username)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
UserDashboard result = new UserDashboard(username);
entities.Database.Connection.Open();
int user_id = UserService.GetUserId(username);
string user_type = entities.Tenants.Where(t => t.Id == user_id).Select(t => t.Tenant_Type.Name).First();
var user_portfolios = entities.Portfolios.Where(ptfolio => ptfolio.user_id == user_id);
foreach (var portfolio in user_portfolios)
{
DashboardItem dashboard_item = new DashboardItem()
{
Portfolio = portfolio.name, Symbols = new List <ctaCOMMON.Charts.Symbol>(), Portfolio_Id = portfolio.Id
};
foreach (var stock in portfolio.Portfolio_Stock)
{
List <Candel> quotes = new List <Candel>();
Candel today_candel = null;
foreach (var quote in stock.Stock.Stock_Quote.Where(s => s.date_round < DateTime.Now.AddDays(-1) || user_type != "FREE").OrderBy(itm => itm.date_round))
{
Candel candel = new Candel()
{
Date = quote.date_round, Open = quote.opening, Close = quote.closing, Minimun = quote.minimun, Maximun = quote.maximun, Volume = (double)quote.volume
};
quotes.Add(candel);
today_candel = candel;
}
SymbolIntradiaryInfo symbolIntradiaryInfo = QuotesService.GetSymbolIntradiaryInfo(stock.stock_id);
if (today_candel != null)
{
symbolIntradiaryInfo.Volume = today_candel.Volume;
symbolIntradiaryInfo.Maximun = today_candel.Maximun;
symbolIntradiaryInfo.Minimun = today_candel.Minimun;
}
ctaCOMMON.Charts.Symbol dashboard_item_symbol = new ctaCOMMON.Charts.Symbol()
{
Symbol_ID = stock.stock_id, Symbol_Name = stock.Stock.symbol, Symbol_Company_Name = stock.Stock.name, Symbol_Market_ID = stock.Stock.Market.Id, Symbol_Market = stock.Stock.Market.name, Quotes = quotes.OrderBy(q => q.Date).ToList(), Intradiary_Info = symbolIntradiaryInfo
};
dashboard_item.Symbols.Add(dashboard_item_symbol);
}
result.AddDashboardItem(dashboard_item);
}
entities.Database.Connection.Close();
DashBoardCache = result;
return(result);
}
}
public static void DeleteTask(VcssTaskModel vcssTask)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
VcssTask task = entities.VcssTasks.Where(t => t.Id == vcssTask.Id).FirstOrDefault();
if (task != null)
{
entities.VcssTasks.Remove(task);
entities.SaveChanges();
}
}
}
public static void MarkAsPendingTask(int taskId, int aux = 0)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
TaskSchedule task = entities.TaskSchedules.Where(t => t.Id == taskId).FirstOrDefault();
if (task != null)
{
task.Status = aux == 0 ? (int)TaskScheduleStatus.InProcess : aux;
entities.SaveChanges();
}
}
}
public static void RemoveTask(int taskId)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
TaskSchedule task = entities.TaskSchedules.Where(t => t.Id == taskId).FirstOrDefault();
if (task != null)
{
entities.TaskSchedules.Remove(task);
entities.SaveChanges();
}
}
}
public static void LogTaskError(string error, string message)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
TashScheduleErrorLog errorlog = new TashScheduleErrorLog()
{
Error = error, Message = message, Date = DateTime.Now
};
entities.TashScheduleErrorLogs.Add(errorlog);
entities.SaveChanges();
}
}
public static List <DashboardReport> GetReports(string reportType)
{
Dictionary <string, List <DashboardReport> > result = new Dictionary <string, List <DashboardReport> >();
using (ctaDBEntities entities = new ctaDBEntities())
{
return(entities.Reports.Where(r => r.Report_Type.name == reportType && r.active).Select(r => new DashboardReport()
{
Id = r.Id, active = r.active, description = r.description, title = r.title, typename = r.Report_Type.name, url = r.url
}).ToList <DashboardReport>());
}
}
public static void ScheduleTask(int vcssTaskInfoId, string data, DateTime scheduledTime)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
VcssTask task = new VcssTask()
{
vcss_task_info_id = vcssTaskInfoId, data = data, scheduled_time = scheduledTime
};
entities.VcssTasks.Add(task);
entities.SaveChanges();
}
}
public static void UpdateBOLSARAuthToken(string authToken)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
List <VcssTaskInfo> tasksInfo = entities.VcssTaskInfoes.Where(t => t.name == "IntradiaryBCBAINDEX" || t.name == "IntradiaryBCBA").ToList();
for (int i = 0; i < tasksInfo.Count; i++)
{
tasksInfo[i].auth_token = authToken;
}
entities.SaveChanges();
}
}
public static void Log(string taskName, string taskData, DateTime taskScheduledTime, string action, string errorMessage, DateTime errorTime)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
VcssTaskLog log = new VcssTaskLog()
{
action = action, error_msg = errorMessage, error_time = errorTime, task_data = taskData, task_name = taskName, task_scheduled_time = taskScheduledTime
};
entities.VcssTaskLogs.Add(log);
entities.SaveChanges();
}
}
public static DateTime GetMarketLocalTime(int marketId, DateTime dateToConvert)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
Market market = entities.Markets.Where(m => m.Id == marketId).FirstOrDefault();
if (market != null)
{
return(dateToConvert.ToUniversalTime().AddHours(market.utc_offset));
}
return(dateToConvert);
}
}
public static void ScheduleTask(TaskScheduleType taskType, string data, DateTime nextTime)
{
using (ctaDBEntities entities = new ctaDBEntities())
{
entities.Database.Connection.Open();
TaskSchedule task = new TaskSchedule()
{
Type = (int)taskType, Data = data, Status = (int)TaskScheduleStatus.Pending, Description = Enum.GetName(typeof(TaskScheduleType), taskType), ExecTime = nextTime
};
entities.TaskSchedules.Add(task);
entities.SaveChanges();
}
}
