/// <summary>
/// Shares the canvas.
/// </summary>
/// <param name="CanvasId">The canvas id.</param>
/// <param name="SharedUserIdList">The shared user id list.</param>
/// <exception cref="System.Exception"></exception>
public void ShareCanvas(int CanvasId, List <int> SharedUserIdList)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
for (int i = 0; i < SharedUserIdList.Count; i++)
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "sharecanvas";
NpgsqlParameter parameter = new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = CanvasId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = SharedUserIdList[i];
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// Deletes the canvas.
/// </summary>
/// <param name="canvasId">The canvas id.</param>
/// <exception cref="System.Exception">Error deleting the canvas. + ex.Message</exception>
public void DeleteCanvas(int canvasId)
{
PostgreSQLDB db = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "delete_canvas";
NpgsqlParameter parameter = new NpgsqlParameter("cid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = canvasId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception("Error deleting the canvas. " + ex.Message);
}
}
/// <summary>
/// Updates the organization Info, updates the name
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public bool UpdateOrganization(OrganizationDto dto)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "update_organization";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dto.Id;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("orgname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dto.Name;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dto.Active;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(false);
}
/// <summary>
/// Adds new org and Admin User object
/// </summary>
/// <param name="dto"></param>
public int AddOrganization(UserOrganizationDto userOrganizationDto)
{
OrganizationDto organizationDto = userOrganizationDto.Organization;
UserDTO userDto = userOrganizationDto.User;
if (userOrganizationDto.User == null)
{
throw new Exception("An organization cannot be added with zero users");
}
int organizationID = -1;
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "add_organization";
NpgsqlParameter parameter = new NpgsqlParameter("orgname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = organizationDto.Name;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("orgdescription", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = organizationDto.Description;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("Usid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = userDto.UserID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("UserNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.UserName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("FirstNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.FirstName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("LastNm", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.LastName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("EmailAdd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.Email;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("phonenbr", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.Phone;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("PwdHash", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = userDto.PasswordHash;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("IsExistingUser", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userDto.IsExistingUser;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
if (userDto.IsExistingUser)
{
parameter = new NpgsqlParameter("ResetPwd", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userDto.ShouldResetPassword;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
else
{
parameter = new NpgsqlParameter("ResetPwd", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = true;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
parameter = new NpgsqlParameter("RId", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = userOrganizationDto.RoleId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("IsActive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = userOrganizationDto.Active;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
organizationID = 1000; //success
}
catch (Exception Ex)
{
throw new Exception(Ex.Message);
}
return(organizationID);
}
public bool ForgotPasswod(string email, string hashedPwd)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
DataSet ds;
try
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_user";
NpgsqlParameter parameter = new NpgsqlParameter("orgid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("email", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = email;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("rid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = -1;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
ds = db.ExecuteDataSet(Command);
//return ds;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
if (ds.Tables[0].Rows.Count > 0)
{
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "forgot_password";
NpgsqlParameter parameter = new NpgsqlParameter("emailadd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = ds.Tables[0].Rows[0]["EMAILADDRESS"];
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("hpassword", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = hashedPwd;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
return(false);
}
return(true);
}
return(false);
}
/// <summary>
/// Updates the user.
/// </summary>
/// <param name="dto">The dto.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public bool UpdateUser(UserOrganizationDto dto)
{
PostgreSQLDB db = new PostgreSQLDB(ConnectionString);
UserDTO User = dto.User;
NpgsqlCommand Command = new NpgsqlCommand();
NpgsqlParameter parameter = null;
if (User.UserEditType == UserEditType.EditingUserInfo)
{
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "update_user";
parameter = new NpgsqlParameter("fname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = User.FirstName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("lname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = User.LastName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("emailadd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = User.Email;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pnumber", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = User.Phone;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("usid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = User.UserID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
//addUserCommand.Parameters.Add("UsrId", User.UserID);
parameter = new NpgsqlParameter("orid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dto.Organization.Id;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("active", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dto.Active;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("roleid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dto.RoleId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
StringBuilder DSIds = new StringBuilder();
foreach (DatasourceDto item in User.DatasourceList)
{
DSIds.Append(item.DatasourceId);
DSIds.Append(",");
}
parameter = new NpgsqlParameter("dsids", NpgsqlTypes.NpgsqlDbType.Varchar);
if (DSIds.ToString().Contains(","))
{
parameter.Value = DSIds.ToString().Substring(0, DSIds.ToString().Length - 1);
}
else
{
parameter.Value = "";
}
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
else
{
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "update_password";
parameter = new NpgsqlParameter("uid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = User.UserID;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("hpassword", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = User.PasswordHash;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
}
try
{
db.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
return(false);
}
return(true);
}
/// <summary>
/// Updates the datasource.
/// </summary>
/// <param name="dsDto">The ds dto.</param>
/// <returns></returns>
/// <exception cref="System.Exception"></exception>
public bool UpdateDatasource(DTO.DatasourceDto dsDto)
{
PostgreSQLDB postDb = new PostgreSQLDB(this.ConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = System.Data.CommandType.StoredProcedure;
Command.CommandText = "update_datasource";
Cryptography cy = new Cryptography();
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.DatasourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbtype", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.Connection.DatabaseType.ToString();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("psinfo", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dsDto.Connection.PersistSecurityInfo.ToString();
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("icatalog", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.DatabaseName);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dsservername", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.ServerName);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbuid", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.UserId);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pwd", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.Password);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.DatabaseObject);
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dsid", NpgsqlTypes.NpgsqlDbType.Integer);
parameter.Value = dsDto.DatasourceId;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("isactive", NpgsqlTypes.NpgsqlDbType.Boolean);
parameter.Value = dsDto.IsActive;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
StringBuilder sb = new StringBuilder();
try
{
foreach (Ewav.DTO.UserDTO item in dsDto.AssociatedUsers)
{
sb.Append(item.UserID);
sb.Append(",");
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
parameter = new NpgsqlParameter("userids", NpgsqlTypes.NpgsqlDbType.Varchar);
if (sb.ToString().Contains(","))
{
parameter.Value = sb.ToString().Substring(0, sb.ToString().Length - 1);
}
else
{
parameter.Value = "";
}
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("pnumber", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = cy.Encrypt(dsDto.Connection.PortNumber.ToString());
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
postDb.ExecuteNonQuery(Command);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
//return false;
}
return(true);
}