using System.Data;

namespace DataTransmission.Joint
{
	public class Organize
	{
		private readonly CommonTool tool = new CommonTool();
		public DataTable GetOrganizeBase(string Pid ="")
		{
			var conn = tool.GetConSqlServer();
			var and = string.IsNullOrEmpty(Pid) ? "" : $" AND SBM_PID = {Pid} ";
			var sql = $@" SELECT *,'' AS NewId, 1 as Frequency FROM  dbo.SYS09_BMDIR WHERE  1=1  AND SBM_ID != 1 AND SBM_Name not in ('热线中心','运营维护') {and} ORDER BY SBM_ID  ";
			return tool.GetDataTable(sql, conn);
		}

		public string GetOrganize() {
			DataTable oldData = GetOrganizeBase();

			DataTable oneData = oldData.AsEnumerable().Where(x=>x.Field<int>("SBM_PID") == 1).CopyToDataTable();
			DataTable Data2 = oldData.Clone();
			DataTable Data3= oldData.Clone();
			DataTable Data4 = oldData.Clone();
			DataTable Data5 = oldData.Clone();
			DataTable Data6 = oldData.Clone();
			var insertSql = $@"INSERT INTO ""public"".""system_organize""
                (
                    ""Id"",""Name"",""ShortName"",""Level"", ""OrgType"",""ParentId"",""ParentName"",""IsEnable"",""IsCenter"",""IsDeleted"",""CreationTime"",""oldBmid""
                )
                VALUES";
			
			insertSql = GetOrganizeSql( ref oneData, insertSql, true,null, oldData,ref Data2);
			if (Data2!= null && Data2.Rows.Count > 0) insertSql = GetOrganizeSql(Data2, insertSql, oldData, oneData,ref Data3,2);
			if (Data3 != null && Data3.Rows.Count > 0) insertSql = GetOrganizeSql(Data3, insertSql, oldData, Data2,ref Data4,3);
			if (Data4 != null && Data4.Rows.Count > 0) insertSql = GetOrganizeSql(Data4, insertSql, oldData, Data3, ref Data5,4);
			if (Data5 != null && Data5.Rows.Count > 0) insertSql = GetOrganizeSql(Data5, insertSql, oldData, Data4,ref Data6,5);
			return insertSql ;
		}

		public string GetOrganizeSql(ref DataTable data,string insertSql,bool isOneGrade,DataRow row,DataTable oldData,ref DataTable Data2) {
			if (data is { Rows.Count: > 0 })
			{
				int num = 0;
				int levelNum = 1;
				foreach (DataRow item in data.Rows)
				{
					item["NewId"] ="001"+ ((num + 1).ToString("D3"));
					var type = item["SBM_TypeName"].ToString() == "市直部门" ? 1 : item["SBM_TypeName"].ToString() == "区县部门" ? 2 : 0;
					var parentId = string.Empty;
					var parentName = string.Empty;
					var pgConn = tool.GetConPgSql();
					var pgOrg = tool.GetDataTable($@" SELECT * FROM ""system_organize"" where ""oldBmid"" =  '{item["SBM_ID"]}'; ", pgConn);
					if (num == 0 && isOneGrade)
					{
						var ccdata = oldData.AsEnumerable().Where(x => x.Field<int>("SBM_PID") == int.Parse(item["SBM_ID"].ToString()));
						if (ccdata.Any())
						{
							DataTable newData = ccdata.CopyToDataTable();
							foreach (DataRow x in newData.Rows)
							{
								Data2.ImportRow(x);
							}
						}
						if (pgOrg is { Rows.Count: > 0 })
						{
							item["NewId"] = pgOrg.Rows[0]["Id"];
							num++;
							continue;
						}
						else {
							var maxPgOrg = tool.GetDataTable($@" SELECT * FROM ""system_organize"" WHERE ""Level"" =1  AND  (""ParentId"" IS NULL OR  ""ParentId""  = '')  ORDER BY  ""Id""  DESC  LIMIT 1 ", pgConn);
							if (maxPgOrg is { Rows.Count: > 0 })
							{
								item["NewId"] =  maxPgOrg.Rows[0]["Id"] + (levelNum.ToString("D3"));
							}
							else
							{
								item["NewId"] = levelNum.ToString("D3");
							}
							//item["NewId"] = "00" + maxPgOrg.Rows[0]["Id"] + levelNum;
							levelNum++;
						}
						insertSql += $@"('{item["NewId"]}','{item["SBM_Name"]}','{item["SBM_Name"]}',{item["SBM_Grade"]},{type},'{parentId}','{parentName}','t','f','f','2023-11-14 00:00:00','{item["SBM_ID"]}')";
						num++;
						continue;
					}
					var cdata = oldData.AsEnumerable().Where(x => x.Field<int>("SBM_PID") == int.Parse(item["SBM_ID"].ToString()));
					if (cdata.Any())
					{
						DataTable newData = cdata.CopyToDataTable();
						foreach (DataRow x in newData.Rows)
						{
							Data2.ImportRow(x);
						}
					}

					if (pgOrg is { Rows.Count: > 0 })
					{
						item["NewId"] = pgOrg.Rows[0]["Id"];
						num++;
						continue;
					}
					else
					{
						var maxPgOrg = tool.GetDataTable($@" SELECT * FROM ""system_organize"" WHERE ""Level"" =1  AND  (""ParentId"" IS NULL OR  ""ParentId""  = '')  ORDER BY  ""Id""  DESC  LIMIT 1 ", pgConn);
						if (maxPgOrg is { Rows.Count: > 0 })
						{
							//item["NewId"] =  maxPgOrg.Rows[0]["Id"] + (levelNum.ToString("D3"));
							item["NewId"] = "00" + (int.Parse(maxPgOrg.Rows[0]["Id"].ToString()) + levelNum).ToString("D3");
						}
						else
						{
							item["NewId"] = levelNum.ToString("D3");
						}
						levelNum++;
					}
					insertSql += $@",('{item["NewId"]}', '{item["SBM_Name"]}','{item["SBM_Name"]}',{item["SBM_Grade"]},{type},'{parentId}','{parentName}','t','f','f','2023-11-14 00:00:00','{item["SBM_ID"]}')";
					num++;
					
				}
			}
			return insertSql;
		}

		public string GetOrganizeSql(DataTable data, string insertSql, DataTable oldData, DataTable pdata,ref DataTable zdata,int level)
		{
			if (data is { Rows.Count: > 0 })
			{
				int num = 0;
				int levelNum = 1;
				foreach (DataRow item in data.Rows)
				{
					var cdata = oldData.AsEnumerable().Where(x => x.Field<int>("SBM_PID") == int.Parse(item["SBM_ID"].ToString()));
					if (cdata.Any())
					{
						DataTable newData = cdata.CopyToDataTable();
						foreach (DataRow x in newData.Rows)
						{
							zdata.ImportRow(x);
						}
					}
					var type = item["SBM_TypeName"].ToString() == "市直部门" ? 1 : item["SBM_TypeName"].ToString() == "区县部门" ? 2 : 0;
					var parentId = string.Empty;
					var parentName = string.Empty;
					var pRow = pdata.AsEnumerable().First(x => x.Field<int>("SBM_ID") == int.Parse(item["SBM_PID"].ToString()));
					var Frequency = int.Parse(pRow["Frequency"].ToString());
					item["NewId"] = pRow["NewId"] + Frequency.ToString("D3");
					parentId = pRow["NewId"].ToString();
					parentName = pRow["SBM_Name"].ToString();
					pRow["Frequency"] = Frequency + 1;
					var pgConn = tool.GetConPgSql();
					var pgOrg = tool.GetDataTable($@" SELECT * FROM ""system_organize"" where ""oldBmid"" =  '{item["SBM_ID"]}'; ", pgConn);
					if (pgOrg is { Rows.Count: > 0 })
					{
						item["NewId"] = pgOrg.Rows[0]["Id"];
						num++;
						continue;
					}
					else
					{
						var maxPgOrg = tool.GetDataTable($@" SELECT * FROM ""system_organize"" WHERE ""Level"" ={level}  AND    ""ParentId""  = '{parentId}'  ORDER BY  ""Id""  DESC  LIMIT 1 ", pgConn);
						if (maxPgOrg is { Rows.Count: > 0 })
						{
							item["NewId"] = "00" + (int.Parse(maxPgOrg.Rows[0]["Id"].ToString()) + levelNum);
						}
						else
						{
							item["NewId"] = parentId + "00" + levelNum;
						}

						levelNum++;
					}
					insertSql += $@",('{item["NewId"]}', '{item["SBM_Name"]}','{item["SBM_Name"]}',{item["SBM_Grade"]},{type},'{parentId}','{parentName}','t','f','f','2023-11-14 00:00:00','{item["SBM_ID"]}')";
					num++;
				}
			}
			return insertSql;
		}

	}
}