|
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Security.Claims;
using webapi_codedocu.Helpers;
using webapp_codedocu.Data;
namespace webapi_codedocu.Controllers
{
[Route("api/[controller]")]
[ApiController]
public
class ServiceController : ControllerBase
{
//--< Variables >--
private readonly ILogger<ArticlesController> _logger;
private readonly ApplicationDbContext _dbContext;
//--</ Variables >--
public
ServiceController(ApplicationDbContext dbContext,
ILogger<ArticlesController> logger)
{
//----< Init >----
_dbContext = dbContext;
_logger = logger;
//----</ Init >----
}
///
<summary>
///
extracts Text from HTML
///
</summary>
///
<returns></returns>
//[Authorize (Roles ="Service")] //only logged in
[Authorize] //*only logged, but check ClaimTypes.Role in Code
[HttpGet("correct_Text")]
public
async Task<ActionResult<string>>Correct_Text()
{
//--------< correct_Text() >--------
_logger.LogWarning("---< Correkt_Text >----");
//< check_owner >
//get client User to check if is owner
var roles = HttpContext.User.FindFirstValue(ClaimTypes.Role);
if
(roles == "")
{
return BadRequest("no rights");
}
else if (roles.Contains("Service")==false)
{
return BadRequest("no rights
for service");
}
//</ check_owner >
int i = 0;
//load articles from db
//load some columns
//var articles = await _dbContext.tbl_Articles.Where(x=>x.Content_Text==null).Take(100).Select(a=>new
Article_Html{ GuidArticle= a.GuidArticle,Content_Html= a.Content_Html ??
""}).ToListAsync();
//load full articles
var articles = await
_dbContext.tbl_Articles.Where(x=>x.Content_Text==null).Take(4000).ToListAsync();
foreach( var article in articles)
{
//----< @Foreach: Articles >----
try
{
i++;
string sHtml = article.Content_Html ?? "";
string sText = Html_Helper.HTML_to_Text(sHtml);
sText = sText.Trim();
//--<
Save_new_Text >--
if (sText.Length >
0) {
if (i % 10 == 0) {
int i20 = 20;
if (sText.Length
< 20) i20 = sText.Length;
_logger.LogDebug("--
Correkt_Text: " + i++ + " : " + sText?.Substring(0, i20));
}
//< update Server
>
//Update only Field
//_dbContext.tbl_Articles.Attach(article).Property(x=>x.Content_Text).IsModified=true;
//await
_dbContext.SaveChangesAsync();
//Update only Field
EntityFrameworkCore.7
_dbContext.tbl_Articles.Where(a
=> a.GuidArticle == article.GuidArticle)
.ExecuteUpdate(b
=> b.SetProperty(u => u.Content_Text, sText) );
//</ update Server
>
//--</
Save_new_Text >--
}
}
catch (Exception ex)
{
return BadRequest("error: " + ex.Message);
throw;
}
//----</ @Foreach: Articles >----
}
//-< Final >-
_logger.LogWarning("---</ Correkt_Text >----");
string sResult = "---- < Fertig >----";
return Ok(sResult); //👍
better loading
//-</ Final >-
//--------</ correct_Text() >--------
}
///
<summary>
///
extracts Text from HTML
///
</summary>
///
<returns></returns>
//[Authorize (Roles
="Service")] //only logged in
//[Authorize] //*only logged, but check ClaimTypes.Role in Code
[HttpGet("correct_folders")]
public
async Task<ActionResult<string>> Correct_Folders()
{
//--------<
correct_Text() >--------
_logger.LogInformation("---< Correct_Folders >----");
//< check database >
if
(_dbContext==null) return BadRequest("Database is not connected");
SqlConnection dbConnection = (SqlConnection)
_dbContext.Database.GetDbConnection()
;
if
(dbConnection == null) return BadRequest("Database Connection is not
ok");
//</ check database >
////< check_permission >
////get client User to check if is owner
//var roles = HttpContext.User.FindFirstValue(ClaimTypes.Role);
//if (roles == null)
//{
// return BadRequest("no
roles, not logged in");
//}
//else if (roles == "")
//{
// return BadRequest("no
rights");
//}
//else if (roles.Contains("Service") == false)
//{
// return BadRequest("no
rights for service");
//}
////</ check_permission >
int i = 0;
if(dbConnection.State!=ConnectionState.Open) dbConnection.Open();
//--< Daten holen >--
string sSQL = "SELECT TOP 5000 * FROM tbl_Articles WHERE Folder
IS NULL";
var command = new SqlCommand(sSQL, dbConnection);
var adapter = new SqlDataAdapter(command);
var dataset = new DataSet();
adapter.Fill(dataset, sSQL);
var table = dataset.Tables[0];
//--</ Daten holen >--
foreach (DataRow row in table.Rows)
{
//----< @Foreach: Articles >----
try
{
i++;
string guidArticle = (string)row["guidArticle"] ??
"";
_logger.LogInformation("xx i=" + i + " guid=" + guidArticle);
string sFolderPath = "";
//< Area >
string sArea = "";
if (row["IDArea"] is not System.DBNull) {
int IDArea=
Convert.ToInt32(row["IDArea"].ToString());
sArea=
get_Area(IDArea) ?? "";
if (sArea !=
"") sFolderPath = "/" + sArea;
}
//</ Area >
//< Folders >
if (row["IDFolder"] is not System.DBNull){
int IDFolder_Article
= Convert.ToInt32((row["IDFolder"]).ToString());
string sFolders =
get_FolderPath(IDFolder_Article) ?? "";
if (sFolders !=
"") sFolderPath += sFolders; //* kommt als /folder1/folder2
}
//</ Folders >
if (sFolderPath != "")
{
//---< update
folders >--
string sHTML =
sFolderPath;
//< remove
Charaters >
sHTML =
Html_Helper.remove_Brackets_Characters (sHTML); //sonderzeichen und Hochkomma
loeschen
sHTML =
Html_Helper.remove_script(sHTML);
sHTML =
Html_Helper.remove_Head(sHTML);
sHTML =
Html_Helper.remove_Tags(sHTML);
sHTML =
Html_Helper.replace_HTML_Umlaute(sHTML);
sHTML =
Html_Helper.remove_Control_Characters(sHTML);
sHTML =
Html_Helper.remove_HTML_Characters(sHTML);
sHTML =
Html_Helper.remove_Punctuation_Mark_Characters(sHTML);
sHTML =
Html_Helper.remove_Brackets_Characters(sHTML);
//</ remove
Charaters >
sFolderPath=sHTML;
string sUpdate =
"UPDATE [tbl_Articles] SET [Folder] = '" + sFolderPath + "'
FROM tbl_Articles WHERE [GuidArticle]='" + guidArticle +
"'";// WHERE Folder = NULL";
var cmdUpdate = new
SqlCommand(sUpdate, dbConnection);
cmdUpdate.ExecuteNonQuery();
}
//--< Save_new_Text >--
if (i % 10 == 0)
{
if
(sFolderPath.Length > 0)
{
_logger.LogDebug("-- Correct_Folders: " + i++ + " :
" + sFolderPath );
}
//< update Server
>
//--</
Save_new_Text >--
}
}
catch (Exception ex)
{
//*either error or sub throw exception
return BadRequest("error:
" + ex.Message);
throw;
}
//----</ @Foreach: Articles >----
}
//-< Final >-
_logger.LogInformation("---</ Correct_Folders >----");
string sResult = "---- < Fertig >----";
return Ok(sResult); //👍
better loading
//-</ Final >-
//--------</ correct_Text() >--------
}
private string? get_FolderPath(int IDStartfolder)
{
//----< get_FolderPath() >----
//< check database >
if
(_dbContext == null) { throw new Exception("Database is not
connected");}
SqlConnection dbConnection =
(SqlConnection)_dbContext.Database.GetDbConnection();
if
(dbConnection == null)
{
throw new Exception("Database Connection is not ok");
}
//</ check database >
string
sFolderPath = "";
int IDParent = IDStartfolder;
while (IDParent != 0)
{
//< lade Sys-daten >
string sql = "SELECT TOP 1 IDFolder,IDParent, IDArea,Title0 FROM
tblSYS_Folders WHERE IDFolder=" + IDParent;
var command = new SqlCommand(sql, dbConnection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
IDParent = Convert.ToInt32(reader["IDParent"]);
string? sFolder = reader["Title0"].ToString();
if (sFolder != null)
{
sFolderPath +=
"/" + sFolder;
}
}
else {
break;
}
//< lade Sys-daten >
}
return sFolderPath;
//----</ get_FolderPath() >----
}
private string? get_Area(int IDArea)
{
//------< get_Area() >------
//< check database >
if
(_dbContext == null) { throw new Exception("Database is not
connected"); }
SqlConnection dbConnection = (SqlConnection)_dbContext.Database.GetDbConnection();
if
(dbConnection == null)
{
throw new Exception("Database Connection is not ok");
}
//</ check database >
string? sArea = "";
//< lade Sys-daten >
string sql = "SELECT TOP 1 Area FROM tblSYS_Areas WHERE
IDArea=" + IDArea;
var command = new SqlCommand(sql, dbConnection);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
sArea = reader["Area"].ToString();
}
//< lade Sys-daten >
return sArea;
//------</ get_Area()
>------
}
}
}
|