using Abp;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

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

		public DataTable GetUser()
		{
			var conn = tool.GetConSqlServer();
			var sql = $@" SELECT * FROM  dbo.SYS05_User 
						LEFT JOIN  dbo.SYS11_Info ON  dbo.SYS05_User.SUser_ID = dbo.SYS11_Info.SInfo_UserID
						 ";
			//WHERE dbo.SYS05_User.SUser_DelFlag = 0 AND dbo.SYS05_User.SUser_ResStatus = 0 AND dbo.SYS05_User.SUser_LStatus = 1
			return tool.GetDataTable(sql, conn);
		}
		public string GetUserSql()
		{
			var insertSql = string.Empty;
			DataTable users = GetUser();
			if (users != null)
			{
				foreach (DataRow row in users.Rows)
				{
					var pgConn = tool.GetConPgSql();
					var id = SequentialGuidGenerator.Instance.Create().ToString("D");
					var pgUser = tool.GetDataTable($@" SELECT * FROM ""user"" WHERE ""OldUserId"" =  '{row["SUser_ID"]}'; ", pgConn);
					if (pgUser != null && pgUser.Rows.Count > 0)
					{
						continue;
					}
					var isDeleted = row["SUser_DelFlag"].ToString() == "0" &&  row["SUser_ResStatus"].ToString() == "0" && row["SUser_LStatus"].ToString() == "1" ? "f" : "t";
					insertSql += $@"INSERT INTO ""public"".""user"" (""Id"",""Name"",""Gender"",""IsDeleted"",""CreationTime"",""CreatorOrgLevel"",""UserType"",""OldUserId"",""OrgId"",""PhoneNo""
									)VALUES('{id}','{row["SUser_Name"]}',2,'{isDeleted}','2024-04-19 00:00:00.000000',1,0,{row["SUser_ID"]},{row["SUser_BMID"]},'{row["SInfo_Mobile"].ToString().Trim()}');";
					insertSql += GetUserAccountSql(row,id, isDeleted);
				}
			}
			return insertSql;
		}

		public string GetUserAccountSql(DataRow item,string id,string isDeleted)
		{
			//自贡 "AQAAAAIAAYagAAAAEJZNauXWsvzHDvSCLEwNjA3qVJcQqEHAexlWDg6ONJJtK5hDNnL8gwwXxwZW5YszfA=="
			//宜宾 "AQAAAAIAAYagAAAAELQ9clcozvhLoTC0pUjUwv40VXsIp3JyxFhEmqVQOmBLMZ7gGmne6cqL9FHXb0CUBQ=="
			var paseword = "AQAAAAIAAYagAAAAELQ9clcozvhLoTC0pUjUwv40VXsIp3JyxFhEmqVQOmBLMZ7gGmne6cqL9FHXb0CUBQ==";

			var insertSql = $@"INSERT INTO ""public"".""account"" (""Id"",""UserName"",""EmailConfirmed"",""PhoneNoConfirmed"",""LockoutEnabled"",""AccessFailedCount"",""ClientId"",
									""Name"",""PasswordChanged"",""Status"",""IsDeleted"",""CreationTime"",""CreatorOrgLevel"",""AccountType"",""PasswordHash"" )
								VALUES('{id}','{item["SUser_LoginName"]}','f','f','t',0,'hotline_server','{item["SUser_Name"]}','t',0,'{isDeleted}','2024-04-19 00:00:00.000000',1,0 ,'{paseword}');";

			var pgConn = tool.GetConPgSql();
			var pgRole = tool.GetDataTable($@" SELECT * FROM ""role"" WHERE ""OldRoleId"" = '{item["SUser_JobID"]}'; ", pgConn);
			insertSql += $@"INSERT INTO ""public"".""account_role"" ( ""AccountId"", ""RoleId"" )
				VALUES
					( '{id}', '{pgRole.Rows[0]["Id"]}' );";
			return insertSql;
		}

		public string GetStaffNoSql() {
			var updateSql = string.Empty;
			var conn = tool.GetConSqlServer();
			var sql = $@" SELECT * FROM dbo.SYS10_Tel ";
			var data = tool.GetDataTable(sql, conn);
			foreach (DataRow row in data.Rows) {
				updateSql += $@"UPDATE ""user"" SET ""StaffNo"" ='{row["STel_UserNum"]}' ,""DefaultTelNo"" ='{row["STel_UserNum"]}' ,""DefaultTelGroup"" ='1'  WHERE ""OldUserId"" ='{row["STel_UserID"]}' ;";
			}
			return updateSql;
		}
	}
}