public IActionResult Filters(int id)
{
var idfilter = new SqlParameter("@id", id);
List <AnalyticData> dataresult = new List <AnalyticData>();
using (var context = new TedAzContext())
{
// var folderDetails = "";
switch (id)
{
case 4:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetFilters](@id)", idfilter).ToList();
break;
case 6:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetFilters](@id)", idfilter).ToList();
break;
case 11:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetFilters](@id)", idfilter).ToList(); //worldcolud
break;
default:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetFilters](@id)", idfilter).ToList();
break;
}
var JSON = JsonSerializer.Serialize(dataresult);
return(Json(JSON));
}
}
public async Task SendMessage(string user, string message)
{
await Clients.All.SendAsync("ReceiveMessage", user);
try
{
Root myDeserializedClass = JsonConvert.DeserializeObject <Root>(user);
Author author = myDeserializedClass.author;
Channel channel = myDeserializedClass.channel;
IEnumerable <Author> authordata = new[] { author };
IEnumerable <Channel> channeldata = new[] { channel };
IEnumerable <Root> rootdata = new[] { myDeserializedClass };
var tableauthor = Extension.ToDataTable(authordata);
var tablechannel = Extension.ToDataTable(channeldata);
var tableroot = Extension.ToDataTable(rootdata);
List <SqlParameter> parameters = Extension.Init()
.Add("@author", tableauthor, "[dbo].[uddtNotesAuthor]")
.Add("@channel", tablechannel, "[dbo].[uddtNotesAuthor]")
.Add("@root", tableroot, "[dbo].[uddtNotesRoot]");
using (var context = new TedAzContext())
{
var data = context.ExecuteProcedure <RootViewModel>("[dbo].[SaveNotes]", parameters);
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "[dbo].[SaveNotes]";
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
await cmd.Connection.OpenAsync();
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
}
}
catch (Exception ex)
{
throw ex;
}
// await Clients.All.SendAsync("ReceiveMessage", user, message);
}
public IActionResult GetReportsInfo()
{
using (var context = new TedAzContext())
{
var q2 = context.GetReportsInfo.FromSqlRaw("exec reports.GetReportsInfo").ToList();
///return View(q2);
string jsondat1 = System.Text.Json.JsonSerializer.Serialize(q2);
return(Json(jsondat1));
}
}
public IActionResult Data(int id, string data)
{
// var JsonData = "";
if (data == null)
{
data = string.Empty;
}
var idnext = new SqlParameter("@id", id);
var filters = new SqlParameter("@pJson", data);
List <AnalyticData> dataresult = new List <AnalyticData>();
using (var context = new TedAzContext())
{
// var folderDetails = "";
switch (id)
{
case 2:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();
break;
case 4:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();
break;
case 6:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();
break;
case 8:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();
break;
case 11:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList(); //worldcolud
break;
case 12:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList(); //worldcolud
break;
default:
dataresult = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();
break;
}
var JSON = JsonSerializer.Serialize(dataresult);
// Histogram myDeserializedClass = JsonConvert.DeserializeObject<Histogram>(JSON);
return(Json(JSON));
}
}
public IActionResult Links()
{
var JsonData = "";
using (var context = new TedAzContext())
{
var data = context.LinkData.FromSqlRaw("exec [dbo].[GetLinks]");
JsonData = JsonSerializer.Serialize(data);
}
return(Json(JsonData));
}
public IActionResult Author()
{
var JsonData = "";
using (var context = new TedAzContext())
{
var data = context.AuthorViewModels.FromSqlRaw("exec [dbo].[GetAuthorInfo]");
JsonData = JsonSerializer.Serialize(data);
}
return(Json(JsonData));
}
public IActionResult DataNotes(int id)
{
var JsonData = "";
using (var context = new TedAzContext())
{
var idnote = new SqlParameter("@id", id);
var data = context.Posts.FromSqlRaw("exec [dbo].[GetNotes] @id", idnote);
JsonData = JsonSerializer.Serialize(data);
}
return(Json(JsonData));
}
public IActionResult DataWords()
{
var JsonData = "";
var idnext = new SqlParameter("@id", 11);
var filters = new SqlParameter("@pJson", string.Empty);
using (var context = new TedAzContext())
{
var data = context.AnalyticData.FromSqlRaw("Select * from [dbo].[fnGetAnalytics](@id,@pJson)", idnext, filters).ToList();//worldcolud
JsonData = JsonSerializer.Serialize(data);
}
return(Json(JsonData));
}
// public static string sqlDataSource = "Data Source=localhost;Initial Catalog = MHM.Accountancy.Database; Integrated Security=True";
// public static string sqlDataSource = "Server=SQL5097.site4now.net;Database=DB_A50752_webhook;User Id = DB_A50752_webhook_admin; Password=webHook@002;";
public override Task OnConnectedAsync()
{
var sdf = new ClaimsPrincipal();
var user = _userManager.GetUserAsync(sdf);
var data = Context.User;
//var data = HttpContext.Session.Get<Models.User>("user");
using (var context = new TedAzContext())
{
//context.Database.ExecuteSqlInterpolated($"exec [dbo].[SaveNotes] @au={}")
//var data=SessionExtension.Get<Models.User>("user").UserName
//Session.Get<TedAzApp.Models.User>("user").UserName;
//var temaName = context.NotesAuthors
// .Where(s => s.AuthorName == "filankes")
// .ToList();
//AddToGroup(temaName.FirstOrDefault().AuthorNickName);
}
return(base.OnConnectedAsync());
}
public async Task <JsonResult> Reportajax(string fromdate, string todate, string reporttype)
{
using (var context = new TedAzContext())
{
if (reporttype == "1")
{
try
{
//DateTime enddate = Convert.ToDateTime(todate.ToString(), CultureInfo.InvariantCulture);
var begidadteparam = new SqlParameter("@fromdate", fromdate);
var enddateparamm = new SqlParameter("@todate", todate);
// var q = context.ReportsMention.FromSqlRaw("exec [reports].[ReportsMentionTest] @fromdate, @todate", begidadteparam, enddateparamm).ToList();
var q = context.MentionReport.FromSqlRaw("exec [reports].[ReportsMention] @fromdate, @todate", begidadteparam, enddateparamm).ToList();
string reporturl = "ReportsInExcel/Ted_Az_Mentions" + fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10) + ".xlsx";
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Qeydlər");
var table = sheet.Cell(1, 1).InsertTable(q, "Report", false);
workbook.SaveAs(reporturl);
var period = fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10);
var reportname = "Ted_Az_Mentions" + fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10) + ".xlsx";
List <SqlParameter> parameters = Extension.Init()
.Add("@userid", 3)
.Add("@reporttype", "Qeydlər")
.Add("@period", period)
.Add("@reporturl", reporturl)
.Add("@reportname", reportname);
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "[reports].[SaveReportInfo]";
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
await cmd.Connection.OpenAsync();
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
if (reporttype == "2")
{
try
{
var begidadteparam = new SqlParameter("@fromdate", fromdate);
var enddateparamm = new SqlParameter("@todate", todate);
var q = context.ReportsMention.FromSqlRaw("exec [reports].[ReportsMentionWithFullTextTest] @fromdate, @todate", begidadteparam, enddateparamm).ToList();
string reporturl = "ReportsInExcel/Ted_Az_MentionsWitFullText" + fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10) + ".xlsx";
var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("Qeydlər(tam mətn)");
var table = sheet.Cell(1, 1).InsertTable(q, "Report", false);
workbook.SaveAs(reporturl);
var period = fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10);
var reportname = "Ted_Az_Mentions" + fromdate.Substring(0, 10) + "-" + todate.Substring(0, 10) + ".xlsx";
List <SqlParameter> parameters = Extension.Init()
.Add("@userid", 3)
.Add("@reporttype", "Qeydlər(tam mətn)")
.Add("@period", period)
.Add("@reporturl", reporturl)
.Add("@reportname", reportname);
using (var cmd = context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "[reports].[SaveReportInfo]";
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
await cmd.Connection.OpenAsync();
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
string status = "Ok";
return(Json(status));
}
public AdminController(TedAzContext context, IWebHostEnvironment appEnvironment)
{
_context = context;
_appEnvironment = appEnvironment;
}
