data-transfer/DataTransmission/Joint/Knowledge.cs

using Abp;
using Castle.MicroKernel.Registration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection.Metadata;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace DataTransmission.Joint
{
	public class Knowledge
	{
		private readonly CommonTool tool = new CommonTool();

		public DataTable GetKnowledge(string st_time, string end_time) {
			var conn = tool.GetConSqlServer();
			var sql = $@"SELECT HR.*,HC.* FROM  dbo.SYS22_HelpRecord  HR 
							LEFT JOIN  dbo.SYS23_HelpContent HC ON HR.SHR_ID =HC.SHC_SRID
							WHERE  SHR_AuditFlag =2 AND HR.SHR_DelFlag = 0 ";
			//HR.SHR_Date >= '{st_time}' AND HR.SHR_Date <= '{end_time}' AND
			return tool.GetDataTable(sql, conn);
		}

		public string GetKnowledgeSql(DataRow item)
		{
			DataTable kType = GetKnowledgeType();
			DataTable pgType = GetPgKnowledgeType();

			//var num = 0;
			//foreach (DataRow item in table.Rows)
			//{
				var knowledgeSql = $@"INSERT INTO ""public"".""knowledge"" (""Id"", ""HotspotId"", ""Code"", ""Title"", ""Summary"", ""Content"", ""PageView"", ""IsPublic"",
								""Status"", ""OnShelfTime"", ""OffShelfTime"", ""ExpiredTime"", ""Version"", ""HotspotExternal"", ""Additions"", ""Knowledges"",
								""WorkflowId"",""ExpiredTimeConfigId"", ""LastModificationTime"", ""IsDeleted"", ""DeletionTime"", ""CreationTime"", ""CreatorOrgId"", ""CreatorId"",
								""AreaId"",""CreatorOrgName"", ""CreatorName"", ""FlowedOrgIds"", ""FlowedUserIds"", ""CreatorOrgLevel"", ""Attribution"", ""SearchNum"",
								""Score"", ""CommentNum"",""SourceOrganizeId"", ""Keywords"", ""FileJson"", ""Renewaln"", ""HandlerUsers"", ""HandlerOrgs"") VALUES";
			
				var pgConn = tool.GetConPgSql();
				var pgKnowledge = tool.GetDataTable($@" SELECT * from knowledge where ""Code"" ='{item["SHR_ID"]}' ", pgConn);
				var Content = item["SHC_TCONTENT"] == null ? "" : item["SHC_TCONTENT"];
				var IsPublic = item["SHR_Public"].ToString() == "0" ? "f" : "t";
				var KnowledgeId = SequentialGuidGenerator.Instance.Create().ToString("D");
		
				var code = item["SHR_ID"].ToString();
			if (pgKnowledge != null && pgKnowledge.Rows.Count > 0)
			{
				//num++;
				//continue;
				return string.Empty;
			}
			//if (num == 0)
			//{

			knowledgeSql += $@"('{KnowledgeId}', '', '{code}', '{item["SHR_Title"]}', NULL, '{Content}', 0, '{IsPublic}',
						3, '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',NULL, NULL, 0, '', NULL, '[]',
						NULL, NULL, '2024-03-08 16:38:10.896183', 'f', NULL, '2024-03-07 16:18:28.240546', '001', '08dbba85-02b4-4c0d-83e9-a6e35977c105',
						'001','热线中心', '', '[]', '[]', 1, '中心知识库', 0, 
						'0', 0, '001', '[]', NULL, 'f', '[]', '[]')
					";
				//	num++;
				//	//continue;
				//}
				//knowledgeSql += $@",('{KnowledgeId}', '', '{code}', '{item["SHR_Title"]}', NULL, '{Content}', 0, '{IsPublic}',
				//		3, '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',NULL, NULL, 0, '', NULL, '[]',
				//		NULL, NULL, '2024-03-08 16:38:10.896183', 'f', NULL, '2024-03-07 16:18:28.240546', '001', '08dbba85-02b4-4c0d-83e9-a6e35977c105',
				//		'001','市民热线服务系统', '', '[]', '[]', 1, '中心知识库', 0, 
				//		'0', 0, '001', '[]', NULL, 'f', '[]', '[]', 
				//		'[{{""Key"":""{typeId}"",""Value"":""{typeName}"",""Splice"":""{spliceName}""}}]')";
				//num++;
			//}
			knowledgeSql += ";";

			var typeT = kType.AsEnumerable().Where(x => x.Field<Int32>("SDICT_ID").ToString() == item["SHR_DictCode"].ToString());
			DataTable pgTypeNew = new DataTable();
			var typeName = string.Empty;
			var spliceName = string.Empty;
			Content = HttpUtility.HtmlDecode(Content.ToString());
			Content = HttpUtility.HtmlDecode(Content.ToString().Replace("&lt;", "<").Replace("&gt;", ">"));
			if (typeT.Any())
			{
				var typeTable = kType.AsEnumerable().Where(x => x.Field<Int32>("SDICT_ID").ToString() == item["SHR_DictCode"].ToString()).CopyToDataTable();
				DataRow typeRow = typeTable.Rows[0];
				spliceName = GetTypeSpliceName2(kType, typeRow["SDICT_Name"].ToString(), typeRow);
				typeName = typeRow["SDICT_Name"].ToString();
				pgTypeNew = GetPgKnowledgeType(spliceName);
				var KnowledgeTypeId = SequentialGuidGenerator.Instance.Create().ToString("D");
				var typeId = pgTypeNew.Rows.Count > 0 ? pgTypeNew.Rows[0]["Id"].ToString() : "";
				var sql = $@"INSERT INTO ""public"".""knowledge_relation_type"" (""KnowledgeId"", ""KnowledgeTypeId"", ""KnowledgeTypeName"", ""KnowledgeTypeSpliceName"")
					VALUES ('{KnowledgeId}', '{KnowledgeTypeId}', '{typeName}', '{spliceName}');";
				knowledgeSql += sql;
			}
		

			return knowledgeSql += ";";
		}

		public DataTable GetKnowledgeType() {
			var conn = tool.GetConSqlServer();
			var sql = $@"SELECT *,'' AS NewTypeId FROM  dbo.SYS15_DICT WHERE SDICT_Type =3 AND  SDICT_Key ='Lore' AND  SDICT_PID !=0 ";
			return tool.GetDataTable(sql,conn);
		}


		public string GetKnowledgeTypeSql() 
		{
			var data = GetKnowledgeType();
			//var sql = $@"INSERT INTO ""public"".""knowledge_type"" (""Id"", ""Name"", ""SpliceName"", ""IsEnable"", ""Sort"", ""ParentId"", ""LastModificationTime"", ""IsDeleted"",
			//			""DeletionTime"", ""CreationTime"", ""CreatorOrgId"", ""CreatorId"", ""AreaId"", ""CreatorOrgName"", ""CreatorName"", ""CreatorOrgLevel"") VALUES ";

			var sql = $@"INSERT INTO ""public"".""knowledge_type"" (""Id"", ""Name"", ""SpliceName"", ""IsEnable"", ""Sort"", ""ParentId"", ""LastModificationTime"", ""IsDeleted"",
						""DeletionTime"", ""CreationTime"", ""CreatorOrgId"", ""CreatorId"", ""AreaId"", ""CreatorOrgName"", ""CreatorName"", ""CreatorOrgLevel"",""OldTypeId"") VALUES ";
			var num = 0;
			foreach (DataRow row in data.Rows)
			{
				row["NewTypeId"] = SequentialGuidGenerator.Instance.Create().ToString("D");
			}
            foreach (DataRow item in data.Rows)
            {
				var pgConn = tool.GetConPgSql();
				var spliceName = GetTypeSpliceName2(data, item["SDICT_Name"].ToString(), item);
				//var pgType = tool.GetDataTable($@" SELECT * FROM knowledge_type WHERE ""Name""='{item["SDICT_Name"]}' and  ""SpliceName"" = '{spliceName}' ", pgConn);
				//if (pgType != null && pgType.Rows.Count > 0)
				//{
				//	continue;
				//}

				DataRow pRow = data.AsEnumerable().FirstOrDefault(x => x.Field<int>("SDICT_ID") == int.Parse(item["SDICT_PID"].ToString()));
				var pid = int.Parse(item["SDICT_PID"].ToString()) == 85 || pRow == null ? "NULL" : $@"'{pRow["NewTypeId"]}'";
				if (num == 0)
				{
					//sql += $@"('{item["NewTypeId"]}', '{item["SDICT_Name"]}', '{spliceName}', 't', 0, {pid}, NULL, 'f', NULL, '2023-11-14 11:14:51.320991', '001', '08daa5f2-1878-4cfa-8764-1244f0229994',
					//	'001', '12345政务服务便民热线', '初始系统管理账号', 1)";
					sql += $@"('{item["NewTypeId"]}', '{item["SDICT_Name"]}', '{spliceName}', 't', {item["SDICT_OrderID"]}, {pid}, NULL, 'f', NULL, '2023-11-14 11:14:51.320991', '001', '08daa5f2-1878-4cfa-8764-1244f0229994',
						'001', '热线中心', '初始系统管理账号', 1,'{item["SDICT_ID"]}')";
					num++;
					continue;
				}
				//sql += $@",('{item["NewTypeId"]}', '{item["SDICT_Name"]}', '{spliceName}', 't', 0, {pid}, NULL, 'f', NULL, '2023-11-14 11:14:51.320991', '001', '08daa5f2-1878-4cfa-8764-1244f0229994',
				//		'001', '12345政务服务便民热线', '初始系统管理账号', 1)";
				sql += $@",('{item["NewTypeId"]}', '{item["SDICT_Name"]}', '{spliceName}', 't', {item["SDICT_OrderID"]}, {pid}, NULL, 'f', NULL, '2023-11-14 11:14:51.320991', '001', '08daa5f2-1878-4cfa-8764-1244f0229994',
						'001', '热线中心', '初始系统管理账号', 1,'{item["SDICT_ID"]}')";
				num++;
			}

            return sql + ";";
		}

		public string GetTypeSpliceName(DataTable data , string spliceName,DataRow row) 
		{
			if (int.Parse(row["SDICT_PID"].ToString()) != 85) {
				DataRow pRow = data.AsEnumerable().FirstOrDefault(x => x.Field<int>("SDICT_ID") == int.Parse(row["SDICT_PID"].ToString()));
				if (pRow != null)
				{
					spliceName += "-" + pRow["SDICT_Name"].ToString();
					if (int.Parse(pRow["SDICT_PID"].ToString()) > 0)
					{
						spliceName = GetTypeSpliceName(data, spliceName, pRow);
					}
				}
			}
			return spliceName;
		}

		public string GetTypeSpliceName2(DataTable data, string spliceName, DataRow row)
		{
			if (int.Parse(row["SDICT_PID"].ToString()) != 85)
			{
				DataRow pRow = data.AsEnumerable().FirstOrDefault(x => x.Field<int>("SDICT_ID") == int.Parse(row["SDICT_PID"].ToString()));
				if (pRow != null)
				{
					spliceName = pRow["SDICT_Name"].ToString() + "-" + spliceName;
					if (int.Parse(pRow["SDICT_PID"].ToString()) > 0)
					{
						spliceName = GetTypeSpliceName2(data, spliceName, pRow);
					}
				}
			}
			return spliceName;
		}

		public DataTable GetPgKnowledgeType(string spliceName ="") 
		{
			var conn = tool.GetConPgSql();
			var sql = $@"SELECT * from knowledge_type ";
			if (!string.IsNullOrEmpty(spliceName)) sql = $@"SELECT * from knowledge_type where ""SpliceName"" ='{spliceName}' ";
			return tool.GetDataTable(sql, conn);
		}

		public DataTable GetPgHotspot(string fullName = "")
		{
			var conn = tool.GetConPgSql();
			var sql = $@"SELECT * from hotspot ";
			if (!string.IsNullOrEmpty(fullName)) sql = $@"SELECT * from hotspot WHERE ""HotSpotFullName"" ='{fullName}' ";
			return tool.GetDataTable(sql, conn);
		}

		public string GetTypeSpliceName(string id) 
		{
			var name = string.Empty;
			var conn = tool.GetConSqlServer();
			var sql = $@" SELECT * FROM  dbo.fn_GetDICTParentIDName({id}) WHERE ParentID != 0 ORDER BY ID ";
			var data = tool.GetDataTable(sql, conn);
			var num = 0;
			if (data != null && data.Rows.Count > 0) 
			{
				foreach (DataRow item in data.Rows ) 
				{
					if (num == 0)
					{
						name += item["Name"].ToString();
						num++;
						continue;
					}
					name += "-"+ item["Name"].ToString();
				}
			
			}
			return name;
		}
	}
}