sanlu_new/sanlu_c#/SunRoxmServer/SunRoxm.BizPeri.User.Access/SunRoxm.BizPeri.User.Access/UserAccess.cs

using SunRoxm.BizPeri.Common.Data;
using System;
using System.Collections;
using System.Data;
using System.Text;

namespace SunRoxm.BizPeri.User.Access
{
	public class UserAccess
	{
		public DataTable GetUserInfo(SqlTask task, string LoginName)
		{
			string strCmdText = $"SELECT * FROM AppUser where (LoginName_User = N'{LoginName}')";
			return task.FillData("AppUser", strCmdText, CommandType.Text);
		}

		public void FillLoginInfo(SqlTask task, LoginInfo loginInfo)
		{
			string strCmdText = string.Format("insert into {0} ({1},{2},{3},{4},{5},{6},{7}) values(convert(uniqueidentifier,'{8}'),convert(datetime,'{9}'),convert(uniqueidentifier,'{10}'),convert(nvarchar(64),'{11}'),convert(datetime,'{12}'),convert(uniqueidentifier,'{13}'),convert(nvarchar(64),'{14}'))", "LoginInfoTable", LoginInfoTable.FD_COMPANYID_LOGININFO, LoginInfoTable.FD_LASTACCESSTIME_LOGININFO, LoginInfoTable.FD_LOGINID_LOGININFO, LoginInfoTable.FD_LOGINNAME_LOGININFO, LoginInfoTable.FD_LOGINTIME_LOGININFO, LoginInfoTable.FD_USERID_LOGININFO, LoginInfoTable.FD_COMPANYDATABASE_LOGININFO, loginInfo.CompanyID, loginInfo.LastAccessTime, loginInfo.LoginID, loginInfo.LoginName, loginInfo.LoginTime.ToString("s"), loginInfo.UserID, loginInfo.CompanyDataBase);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void FillLoginInfo(SqlTask task, LoginInfo loginInfo, string serverName)
		{
			string strCmdText = string.Format("insert into {0} ({1},{2},{3},{4},{5},{6}) values(convert(nvarchar(64),'{7}'),convert(datetime,'{8}'),convert(uniqueidentifier,'{9}'),convert(nvarchar(64),'{10}'),convert(datetime,'{11}'),convert(uniqueidentifier,'{12}'))", "LoginInfoTable", "ServerName_LoginInfo", LoginInfoTable.FD_LASTACCESSTIME_LOGININFO, LoginInfoTable.FD_LOGINID_LOGININFO, LoginInfoTable.FD_LOGINNAME_LOGININFO, LoginInfoTable.FD_LOGINTIME_LOGININFO, LoginInfoTable.FD_USERID_LOGININFO, serverName, loginInfo.LastAccessTime, loginInfo.LoginID, loginInfo.LoginName, loginInfo.LoginTime.ToString("s"), loginInfo.UserID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLoginInfo(SqlTask task, LoginInfo loginInfo)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}')", "LoginInfoTable", LoginInfoTable.FD_LOGINID_LOGININFO, loginInfo.LoginID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLoginInfo(SqlTask task)
		{
			string strCmdText = string.Format("delete from {0}", "LoginInfoTable");
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLoginInfo(SqlTask task, string CurrentDb, string ManageDb)
		{
			string strCmdText = string.Format("use [{1}] delete from {0} use [{2}]", "LoginInfoTable", CurrentDb, ManageDb);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLoginInfo(SqlTask task, Guid UserID, string Password)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}')", "LoginInfoTable", LoginInfoTable.FD_USERID_LOGININFO, UserID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLoginInfo(SqlTask task, Guid UserID, string CurrentDb, string ManageDb)
		{
			string strCmdText = string.Format("use [{3}] delete from {0} where {1} = convert(uniqueidentifier,'{2}') use [{4}]", "LoginInfoTable", LoginInfoTable.FD_USERID_LOGININFO, UserID, CurrentDb, ManageDb);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public DataTable GetLoginInfo(SqlTask task)
		{
			string strCmdText = string.Format("select * from {0}", "LoginInfoTable");
			return task.FillData("LoginInfoTable", strCmdText, CommandType.Text);
		}

		public void FillLockInfo(SqlTask task, LoginInfo loginInfo, Guid RecordID, string FunCode)
		{
			string strCmdText = string.Format("insert into {0} ({1},{2},{3},{7}) values(convert(nvarchar(64),'{4}'),convert(uniqueidentifier,'{5}'),convert(uniqueidentifier,'{6}'),convert(uniqueidentifier,'{8}'))", "LockInfoTable", LockInfoTable.FD_FUNCODE_LOCKINFOTABLE, LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LockInfoTable.FD_RECORDID_LOCKINFOTABLE, FunCode, loginInfo.LoginID, RecordID, LockInfoTable.FD_USERID_LOCKINFOTABLE, loginInfo.UserID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, Guid RecordID, string FunCode)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}') and {3} = convert(nvarchar(64),'{4}')", "LockInfoTable", LockInfoTable.FD_RECORDID_LOCKINFOTABLE, RecordID, LockInfoTable.FD_FUNCODE_LOCKINFOTABLE, FunCode);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task)
		{
			string strCmdText = string.Format("delete from {0}", "LockInfoTable");
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, string CurrentDb, string ManageDb)
		{
			string strCmdText = string.Format("use [{1}] delete from {0} use [{2}]", "LockInfoTable", CurrentDb, ManageDb);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, Guid LoginID, Guid UserID)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}') and {3} = convert(uniqueidentifier,'{4}')", "LockInfoTable", LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LoginID, LockInfoTable.FD_USERID_LOCKINFOTABLE, UserID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, string LoginName)
		{
			string strCmdText = string.Format("delete from {0} where {1} in (select {2} from  {3} where {4} =  convert(nvarchar(64),'{5}')) ", "LockInfoTable", LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LoginInfoTable.FD_LOGINID_LOGININFO, "LoginInfoTable", LoginInfoTable.FD_LOGINNAME_LOGININFO, LoginName);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, string LoginName, string CurrentDb, string ManageDb)
		{
			string strCmdText = string.Format("use [{6}] delete from {0} where {1} in (select {2} from  {3} where {4} =  convert(nvarchar(64),'{5}')) use [{7}]", "LockInfoTable", LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LoginInfoTable.FD_LOGINID_LOGININFO, "LoginInfoTable", LoginInfoTable.FD_LOGINNAME_LOGININFO, LoginName, CurrentDb, ManageDb);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, Guid LoginID)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}') ", "LockInfoTable", LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LoginID);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public void DeleLockInfo(SqlTask task, Guid LoginID, Guid UserID, Guid RecordID, string FunCode)
		{
			string strCmdText = string.Format("delete from {0} where {1} = convert(uniqueidentifier,'{2}') and {3} = convert(uniqueidentifier,'{4}') and {5} = convert(uniqueidentifier,'{6}') and {7} = convert(nvarchar(64),'{8}')", "LockInfoTable", LockInfoTable.FD_LOGINID_LOCKINFOTABLE, LoginID, LockInfoTable.FD_USERID_LOCKINFOTABLE, UserID, LockInfoTable.FD_RECORDID_LOCKINFOTABLE, RecordID, LockInfoTable.FD_FUNCODE_LOCKINFOTABLE, FunCode);
			task.OperationData(strCmdText, CommandType.Text);
		}

		public DataTable GetLoginInfoByLockInfo(SqlTask task, Guid RecordID, string FunCode)
		{
			string strCmdText = string.Format("select * from {0} A inner join {1} B on A.LoginID_LoginInfo = B.LoginID_LockInfoTable where B.RecordID_LockInfoTable = convert(uniqueidentifier,'{2}')  and B.Funcode_LockInfoTable = convert(nvarchar(64),'{3}')", "LoginInfoTable", "LockInfoTable", RecordID, FunCode);
			return task.FillData("LoginInfoTable", strCmdText, CommandType.Text);
		}

		public DataTable GetAllDepot(SqlTask task)
		{
			string strCmdText = $"select ID_Location,No_location,Name_Location from location where subjectionid_location is null order by no_location";
			return task.FillData("Location", strCmdText, CommandType.Text);
		}

		public DataTable GetUserList(SqlTask task)
		{
			string strCmdText = $"select No_User,LoginName_User from AppUser where IsSuspend_User = 0 order by No_User";
			return task.FillData("AppUser", strCmdText, CommandType.Text);
		}

		public Hashtable DataBaseInfo(SqlTask task, string CompanyName, bool bCompanyName)
		{
			Hashtable hashtable = new Hashtable();
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.Append("declare @DBName nvarchar(64)");
			if (bCompanyName)
			{
				stringBuilder.AppendFormat(" select @DBName = DataBase_Company from SunRoxmManager.dbo.Company where Name_Company = '{0}' ", CompanyName);
			}
			else
			{
				stringBuilder.AppendFormat(" set @DBName  = '{0}'", CompanyName);
			}
			stringBuilder.Append(" SELECT  filename ,name FROM master.dbo.sysdatabases where name = @DBName ");
			stringBuilder.Append(" SELECT @@VERSION AS 'SQL Server Version' ");
			stringBuilder.Append(" EXEC master..xp_servicecontrol QueryState, SQLServerAgent ");
			stringBuilder.Append(" SELECT  crdate FROM master.dbo.sysdatabases where name = @DBName");
			stringBuilder.Append(" select top 1 backup_finish_date from msdb.dbo.backupset where database_name = @DBName order by backup_finish_date desc");
			DataSet dataSet = task.FillDataSet("AppUser", stringBuilder.ToString(), CommandType.Text);
			if (dataSet.Tables.Count > 0)
			{
				string text = "";
				string text2 = "";
				string value = "";
				if (dataSet.Tables[0].Rows.Count > 0)
				{
					text = dataSet.Tables[0].Rows[0][0].ToString();
					value = dataSet.Tables[0].Rows[0][1].ToString();
				}
				if (dataSet.Tables[1].Rows.Count > 0)
				{
					text2 = dataSet.Tables[1].Rows[0][0].ToString();
				}
				hashtable.Add("DataBaseName", value);
				hashtable.Add("DataBaseFilePath", text.Substring(0, text.LastIndexOf("\\")));
				hashtable.Add("DataBaseVersion", text2.Substring(0, text2.LastIndexOf("(")));
				if (dataSet.Tables[2].Rows.Count > 0)
				{
					hashtable.Add("SQL Server Agent", dataSet.Tables[2].Rows[0][0]);
				}
				else
				{
					hashtable.Add("SQL Server Agent", "");
				}
				if (dataSet.Tables[3].Rows.Count > 0)
				{
					hashtable.Add("DBCreateDate", dataSet.Tables[3].Rows[0][0].ToString());
				}
				else
				{
					hashtable.Add("DBCreateDate", "");
				}
				if (dataSet.Tables[4].Rows.Count > 0)
				{
					hashtable.Add("DBBackDate", dataSet.Tables[4].Rows[0][0].ToString());
				}
				else
				{
					hashtable.Add("DBBackDate", "");
				}
			}
			return hashtable;
		}

		public string DataBaseVersion(SqlTask task)
		{
			string strCmdText = $"SELECT @@VERSION AS 'SQL Server Version'";
			DataTable dataTable = task.FillData("AppUser", strCmdText, CommandType.Text);
			if (dataTable.Rows.Count > 0)
			{
				string text = dataTable.Rows[0][0].ToString();
				return text.Substring(0, text.IndexOf("("));
			}
			return string.Empty;
		}

		public string SQLServerAgent(SqlTask task)
		{
			string strCmdText = $"EXEC master..xp_servicecontrol QueryState, SQLServerAgent";
			DataTable dataTable = task.FillData("AppUser", strCmdText, CommandType.Text);
			if (dataTable.Rows.Count > 0)
			{
				return dataTable.Rows[0][0].ToString();
			}
			return string.Empty;
		}

		public DataTable GetCompanyName(SqlTask task, string CompanyNo)
		{
			string strCmdText = $"select Name_Company From SunRoxmManager.DBO.Company Where No_Company = '{CompanyNo}'";
			return task.FillData("Company", strCmdText, CommandType.Text);
		}
	}
}