/// <summary>
/// Gets the columns for datasource.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public DataTable GetColumnsForDatasource(DataRow[] dr)
{
Security.Cryptography cy = new Security.Cryptography();
string tableName = cy.Decrypt(dr[0]["DatabaseObject"].ToString());
string externalConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL, dr);
string query = "";
if (tableName.SQLTest())
{
query = string.Format("SELECT * FROM ( {0} ) as table1 limit 1; ", tableName);
}
else
{
query = string.Format("SELECT * FROM {0} limit 1;", tableName);
}
DataTable dt = new DataTable();
PostgreSQLDB pstdb = new PostgreSQLDB(externalConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandText = query;
Command.CommandType = CommandType.Text;
dt = pstdb.ExecuteDataSet(Command).Tables[0];
return(dt);
}
/// <summary>
/// Gets the columns for datasource.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public DataTable GetColumnsForDatasource(DataRow[] dr)
{
Security.Cryptography cy = new Security.Cryptography();
string tableName = cy.Decrypt(dr[0]["DatabaseObject"].ToString());
string externalConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.SQLServer, dr);
string query = "";
// if (tableName.ToLower().Contains("select"))
if (tableName.SQLTest())
{
query = string.Format("SELECT TOP 1 * FROM ( {0} ) as table1 ", tableName);
}
else
{
query = string.Format("SELECT TOP 1 * FROM {0}", tableName);
}
DataTable dt = new DataTable();
SqlDatabase sqd = new SqlDatabase(externalConnectionString);
//dt = sqd.ExecuteDataSet("usp_read_columns_for_datasource", tableName, tableName.SQLTest()).Tables[0];
dt = sqd.ExecuteDataSet(CommandType.Text, query).Tables[0];
return(dt);
}
/// <summary>
/// Adds new org and Admin User object
/// </summary>
/// <param name="dto"></param>
public int AddOrganization(UserOrganizationDto userOrganizationDto)
{
OrganizationDto organizationDto = userOrganizationDto.Organization;
Cryptography Cryptography = new Security.Cryptography();
UserDTO userDto = userOrganizationDto.User;
if (userOrganizationDto.User == null)
{
throw new Exception("An organization cannot be added with zero users");
}
int organizationID = -1;
SqlDatabase db = new SqlDatabase(ConnectionString);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "usp_add_organization";
command.Parameters.Add(new SqlParameter("@OrganizationName", organizationDto.Name));
command.Parameters.Add(new SqlParameter("@OrganizationDescription", ""));
command.Parameters.Add(new SqlParameter("@OrganizationKey", Cryptography.Encrypt(Guid.NewGuid().ToString())));
command.Parameters.Add(new SqlParameter("@UserId", userDto.UserID));
command.Parameters.Add(new SqlParameter("@UserNm", userDto.UserName));
command.Parameters.Add(new SqlParameter("@FirstNm", userDto.FirstName));
command.Parameters.Add(new SqlParameter("@LastNm", userDto.LastName));
command.Parameters.Add(new SqlParameter("@EmailAdd", userDto.Email));
command.Parameters.Add(new SqlParameter("@PhoneNbr", userDto.Phone));
command.Parameters.Add(new SqlParameter("@PwdHash", userDto.PasswordHash));
command.Parameters.Add(new SqlParameter("@IsExistingUser", userDto.IsExistingUser));
if (userDto.IsExistingUser)
{
command.Parameters.Add(new SqlParameter("@ResetPwd", userDto.ShouldResetPassword));
}
else
{
command.Parameters.Add(new SqlParameter("@ResetPwd", true));
}
command.Parameters.Add(new SqlParameter("@RoleId", userOrganizationDto.RoleId));
command.Parameters.Add(new SqlParameter("@IsActive", userOrganizationDto.Active));
try
{
db.ExecuteNonQuery(command);
organizationID = 1000; //success
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
}
return(organizationID);
}
/// <summary>
/// Gets the external connection string.
/// </summary>
/// <param name="dataSourceName">Name of the data source.</param>
/// <param name="tableName"></param>
/// <returns></returns>
public string GetExternalConnectionString(string dataSourceName, out string tableName) // out DataBaseTypeEnum databaseType)
{
DataTable dtGet;
PostgreSQLDB ewavDB = new PostgreSQLDB(this.MetaDataConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_external_connec_str";
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dataSourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
dtGet = ewavDB.ExecuteDataSet(Command).Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
string extConnectionString = " ";
extConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL,
new DataRow[] { dtGet.Rows[0] });
Ewav.Security.Cryptography cy = new Security.Cryptography();
tableName = cy.Decrypt(dtGet.Rows[0]["DatabaseObject"].ToString());
return(extConnectionString);
}
public static void InitConnections( )
{
STDataServersController serverCtrl = new STDataServersController();
foreach (STDataServerSyncsInfo syncInfo in new STDataServerSyncsController().GetListAllObjects().Cast <STDataServerSyncsInfo>().ToList())
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
continue;
}
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
#region Init Linked Server
Security.Cryptography cryp = new Security.Cryptography();
String strPassword = cryp.Decrypt(server.EncryptedPassword);
String strQuery = String.Format(@"IF EXISTS(SELECT * FROM sys.servers WHERE name = '{0}') EXEC master.sys.sp_dropserver '{0}','droplogins'
EXEC master.dbo.sp_addlinkedserver
@server = '{0}',
@srvproduct=N'MSSQL',
@provider=N'SQLNCLI',
@provstr=N'PROVIDER=SQLOLEDB;SERVER={1}'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='{0}',
@useself=N'False',@locallogin=NULL,@rmtuser=N'{2}',@rmtpassword='******'", server.Name, server.ServerAddress, server.UserName, strPassword);
BusinessObjectController.RunQuery(strQuery);
#endregion
InitCompanyConnection(server.STDataServerID);
InitSystemConnection(server.STDataServerID);
}
}
public static bool ChangePassword(String strDatabase, String strUserNo, String strOldPassword, String strNewPassword)
{
if (!ConnectDatabase(strDatabase))
{
return(false);
}
Security.Cryptography crypto = new Security.Cryptography();
String strOldEncryptedPass = crypto.Encrypt(strOldPassword);
ADUsersInfo user = new ADUsersController().GetObjectByNo(strUserNo) as ADUsersInfo;
if (user == null || strOldEncryptedPass != user.Password)
{
ABCHelper.ABCMessageBox.Show(LoginForm, "Thông tin người dùng, mật khẩu không đúng", "Đổi mật khẩu", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
user.Password = crypto.Encrypt(strNewPassword);
BusinessObjectController.RunQuery(String.Format(@"UPDATE ADUsers SET Password='******' ,UpdateTime=GETDATE() WHERE ADUserID = '{1}'", user.Password, user.ADUserID));
ABCHelper.ABCMessageBox.Show(LoginForm, "Đổi mật khẩu thành công", "Đổi mật khẩu", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(true);
}
public static void Login(LoginType loginType, String strDatabase, String strUserNo, String strPassword)
{
if (!ConnectDatabase(strDatabase))
{
return;
}
String strEncryptedPass = new Security.Cryptography().Encrypt(strPassword);
ADUsersInfo user = new ADUsersController().GetObject(String.Format(@"SELECT * FROM ADUsers WHERE No='{0}' AND ABCStatus ='Alive' AND Active =1 ", strUserNo)) as ADUsersInfo;
if (user == null || strEncryptedPass != user.Password)
{
ABCHelper.ABCMessageBox.Show(LoginForm, "Thông tin người dùng, mật khẩu không đúng", "Đăng nhập", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (LoginForm != null)
{
LoginForm.Close();
LoginForm.Dispose();
}
Application.DoEvents();
ABCScreen.SplashUtils.ShowSplash(loginType);
ABCControls.UICaching.InitCachingPresentControls();
#region Section
CurrentUser = user;
if (CurrentUser.FK_ADUserGroupID.HasValue)
{
CurrentUserGroup = new ADUserGroupsController().GetObjectByID(CurrentUser.FK_ADUserGroupID.Value) as ADUserGroupsInfo;
}
ABCUserProvider.CurrentUserName = CurrentUser.No;
if (CurrentUser.FK_HREmployeeID.HasValue)
{
try
{
ABCUserProvider.CurrentEmployee = new HREmployeesController().GetObjectByID(CurrentUser.FK_HREmployeeID.Value) as HREmployeesInfo;
if (ABCUserProvider.CurrentEmployee != null)
{
ABCUserProvider.CurrentEmployeeName = ABCUserProvider.CurrentEmployee.Name;
if (ABCUserProvider.CurrentEmployee.FK_GECompanyUnitID.HasValue)
{
ABCUserProvider.CurrentCompanyUnit = new GECompanyUnitsController().GetObjectByID(ABCUserProvider.CurrentEmployee.FK_GECompanyUnitID.Value) as GECompanyUnitsInfo;
}
}
}catch (Exception ex)
{
}
}
StartOnlineTimer();
if (loginType == LoginType.ERP)
{
ABCApp.ABCAppHelper.Instance.StartSection();
}
else if (loginType == LoginType.Studio)
{
ABCStudio.ABCStudioHelper.Instance.StartSection();
}
ABCScreen.SplashUtils.CloseSplash();
#endregion
}
